How to Fix Multiple Records in Power BI

Recently I was working in Power BI with Sales Order Archives data in Dynamics 365 Business Central, creating visuals with sales order amounts as my measure. I then found I had multiple records with different dates or versions.

When I looked at my visuals, Power BI showed much higher numbers than I expected. I looked at the data and found that there were multiple records for the same sales order number, but different versions and dates archived. Some orders had 4 or 5 different versions, as seen in the screenshot below:

Recent Records Power BI

So how do you keep or use the most recent records only, to avoid this double counting issue? The answer is you can do it with either Power Query or Data Analysis Expressions (DAX).

Power Query Method

Right click on No > Group By:

Recent Records Power BI

Enter New column name, select “All Row” for Operation:

Recent Records Power BI

Rows are grouped by No as you see below. One row per one sales order number:

Recent Records Power BI

Add a Custom column:

Recent Records Power BI

Enter new column name and enter formula below:

Recent Records Power BI

You will see new column with Record data type:

Recent Records Power BI

Expand “The Latest Row” column:

Recent Records Power BI Recent Records Power BI

Data Analysis Expressions (DAX) Method

You can create a calculated column below in DAX to use it as a filter or slicer to keep the most records only.

Recent Records Power BI Recent Records Power BI

Which method works better for you all depends on your business and goals. If you need to analyze your data with all versions, the Data Analysis Expressions (DAX) method would be better. Otherwise, the Power Query method is better because it reduces number of rows and the size of the dataset.

If you have any questions about using Power BI, please get in touch with us.

Get the Full Gartner Magic Quadrant for BI Report

Get the full report on the top BI Vendors.

Get the Report

Get the Full Gartner Magic Quadrant for BI Report

Get the Report