Power BI

Pulkit Gulati

Sort By Day of Week PowerBI

It’s a common scenario where you’d want to display data in a chart based on Day of the Week and it obviously will look good if sorted by the Day of Week starting say Monday.

In this scenario, my Model has sample Incidents data used by a team for the number of Incidents raised over a period of time. I’ve created a Measure for the Total Count of Incidents as below:

Total Count = COUNT(IncidentsData[Incident #])

I’m using a Stacked Column chart for the display purpose, so I need to create a column Weekday that will be calculated from the Created Date of the Incident.

WEEKDAY = FORMAT('IncidentsData'[Created Date & Time],"DDDD")

Create another column that shows the default number of the Weekday as below:

WEEKDAY-NR = WEEKDAY(IncidentsData[Created Date & Time], 2)

Now select the column Weekday column in the Model, Go to Sort by Column menu and Select the column Weekday-NR as the Sort order. This tells Power BI to sort Weekday Column by Weekday-NR.

Make sure to select Weekday as your sort column in the Stacked Column chart.

Advertisement
Pulkit Gulati

Replace Error values in Power BI data model

Here is the Sample Excel data source I’ve created for this Example. Here we will replace the error values in our Query Editor. MyCol3 is Sum of the first 2 columns. Once imported in the data model in Power BI, the 2 rows with values a and b in MyCol2 will show Error in MyCol3.

Import the Excel as below:
Get Data->Excel->Select the Excel file from which you want to import->Select the Sheet you created with the Preview.

Importing the sheet shows the following dialog box:

The data model is shown as below in Power BI:

Right-click on MyCol3 and select Edit Query. This will open the Query Editor.

In the Query Editor, right-click on MyCol3 and Select Replace Errors:

Same option is available in Replace Values drop-down menu under Home Tab in the Ribbon.

Enter the required value to replace with Error and click OK:

The Final values in the data model will be visible as below:


The Applied Steps in the Query Editor will be shown as below for Sheet1 data model:

Click on Close & Apply in the Home Tab to save your changes.

Similar steps can be used to Replace Error values in Excel using Power Query.