Forum Discussion
Show most recent data
- 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 - I just had a go trying to figure out how to join in 2 fields and I ended with the same result as you and now the rest of the steps are obsolete.
CalcMaxVersion = Table.Group(#"NameOfPreviousStep", {"Sales Orders"}, {{"MaxVersion", each List.Max([#"Version number"])}}),
#"Add Column" = Table.Join(#"Renamed Columns", {"Sales Orders", "Version number"}, CalcMaxVersion, {"Sales Orders", "MaxVersion"}, JoinKind.Inner)
in
#"Add Column"Hi,
I have the same situation as the original poster but I keep having syntax issues. Please would you help?
JobRef ShippingManifest_Id
79235 125 Discard
79235 113 Discard
79235 206 Display
80123 252 Discard
80123 351 Display
90512 231 Display
98512 112 Discard
99111 111 Do nothing