Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Show most recent data

Going off this example: http://community.powerbi.com/t5/Desktop/Comparing-Sales-Orders-rows-and-keeping-the-highest-version/td-p/12729 I have the same issues except I have an extra column: ...
Need Help
Tips and Tricks
Tutorial Requests
  • sdjensen's avatar
    sdjensen
    10 years ago

    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's avatar
sdjensen
Solution Sage
10 years ago

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"
ImkeF's avatar
ImkeF
Community Champion
10 years ago

Hi there, for this kind of filter-operation the JoinKind.Inner-type is very handy. It makes all the following steps obsolete:

 

CalcMaxVersion = Table.Group(#"NameOfPreviousStep", {"Sales Orders"}, {{"MaxVersion", each List.Max([#"Version number"])}}),
    #"Add Column" = Table.NestedJoin(#"Renamed Columns", "Sales Orders", CalcMaxVersion, "Sales Orders", "MaxVersion", JoinKind.Inner),

in
    #"Add Column"