Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Show most recent data

Going off this example:

http://community.powerbi.com/t5/Desktop/Comparing-Sales-Orders-rows-and-keeping-the-highest-version/...



I have the same issues except I have an extra column:

Example: 

Sales Order        Date                           Version number

M00123456      1/12/2015                  1           <--- Do nothing

M00234567      2/12/2016                  1           <--- Discard

M00234567      2/12/2016                  2           <--- Display this one

M00345678      4/23/2016                  1           <--- Discard 

M00345678      4/23/2016                  2           <--- Discard 

M00345678      5/12/2016                  3           <--- Display this one

 

Is there a way to keep only the latest with this data? I  tried grouping it and filtering but since the version and dates are different it keeps one from each version, and I just want to keep the latest for each sales order.

1 ACCEPTED SOLUTION
sdjensen
Solution Sage
Solution Sage

You could perhaps try something like this - basically I use the same group by, but then in the next step go back to the previous step and then join with the values from the group by then calculated a value for each row that is equal to the max version number per Sales Order and then remove the rows that does not match. You can always add extra steps to remove columns you don't want to keep.

 

 

CalcMaxVersion = Table.Group(#"NameOfPreviousStep", {"Sales Orders"}, {{"MaxVersion", each List.Max([#"Version number"])}}),
    #"Add Column" = Table.NestedJoin(#"Renamed Columns", "Sales Orders", CalcMaxVersion, "Sales Orders", "MaxVersion", 1),
    #"Expanded MaxVersion" = Table.ExpandTableColumn(#"Add Column", "MaxVersion", {"MaxVersion"}, {"MaxVersion.MaxVersion"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded MaxVersion", "RowsToKeep", 
        each if [Version number] = [MaxVersion.MaxVersion] then "Keep" 
            else if [Version number] <> [MaxVersion.MaxVersion] then "Discard" 
            else null ),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([RowsToKeep] = "Keep"))
in
    #"Filtered Rows"
/sdjensen

View solution in original post

15 REPLIES 15

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors