Forum Discussion
Show most recent data
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"
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"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"ImkeF - Could you please elaborate how the following steps are obsolete?
This is the result from my origional code where I only have 3 rows left after filtering the calculated column "RowsToKeep"
If I change the code and stop the script withe "Add Column" as the last step using your code we get this result, so now we have 6 rows and not just the rows with max Version Number per Sales Order.
I agree that instead of Table.NestedJoin it might be better to use this code
#"Add Column" = Table.Join(#"Renamed Columns", "Sales Orders", CalcMaxVersion, "Sales Orders", JoinKind.Inner)
- this will make the Table.ExpandTableColumn obsolute, but there is still a need for filtering the table so only the 3 rows with the max Version Number is the end result.
Result with Join instead of NestedJoin:
Sorry, didn't see that there was a field missing on which to combine:
#"Add Column" = Table.Join(#"Renamed Columns", {"Sales Orders", "Version number"}, CalcMaxVersion, {"Sales Orders", "MaxVersion"}, JoinKind.Inner)
