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:

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:

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

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

Add a Custom column:

Enter new column name and enter formula below:

You will see new column with Record data type:

Expand “The Latest Row” column:

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.

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.