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"
Hi,
please check how this works for you:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrc0MjZV0lEyNAKRLpnFyYlFKUqxOkgyhsY4ZIwMzCAyBTmJlWAZCwNDI5BqI1MjND0wGWNTQzQ9lgamhiDVRsa4ZAwN0U2ztDQ0NATLgPXkK+Tll2Rk5qUrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [JobRef = _t, Shipping = _t, Manifest_Id = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"JobRef", Int64.Type}, {"Shipping", Int64.Type}, {"Manifest_Id", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"JobRef"}, {{"Shipping", each List.Max([Shipping]), type number}})
in
#"Grouped Rows"Imke Feldmann
www.TheBIccountant.com -- How to integrate M-code into your solution -- Check out more PBI- learning resources here
Hi,
Many thanks for your help. I was hoping to follow on from the previous example but i have a "Token Comma expected" error - are you able to see what is wrong in the code below?
CalcMaxVersion = Table.Group(#"Renamed Columns", {"JobRef"}, {{"MaxVersion", each List.Max([#"ShippingManifest_Id"])})),
#"Add Column" = Table.Join(#"Renamed Columns", {"JobRef", "ShippingManifest_Id"}, CalcMaxVersion, {"JobRef", "MaxVersion"}, JoinKind.Inner)
in
#"Add Column"
- ImkeF8 years agoCommunity Champion
Yes, that error-message normally stands for an issue with brackets. It looks as if there is one surplus closing bracket ")" in step CalMaxVersion. Try this code instead:
CalcMaxVersion = Table.Group(#"Renamed Columns", {"JobRef"}, {{"MaxVersion", each List.Max([#"ShippingManifest_Id"])}), #"Add Column" = Table.Join(#"Renamed Columns", {"JobRef", "ShippingManifest_Id"}, CalcMaxVersion, {"JobRef", "MaxVersion"}, JoinKind.Inner) in #"Add Column" - gjh1008 years agoNew Member
thanks, made the change but still getting "token comma expcted" errror
- ImkeF8 years agoCommunity Champion
Must have deleted a curly bracket. Try this:
CalcMaxVersion = Table.Group(#"Renamed Columns", {"JobRef"}, {{"MaxVersion", each List.Max([#"ShippingManifest_Id"]) }} ), #"Add Column" = Table.Join(#"Renamed Columns", {"JobRef", "ShippingManifest_Id"}, CalcMaxVersion, {"JobRef", "MaxVersion"}, JoinKind.Inner) in #"Add Column"Otherwise just check that every opened bracket is closed properly.
- gjh1008 years agoNew Member
I've checked all brackets now. Token EopF expcted is the new error
- ImkeF8 years agoCommunity Champion
Probably an error in the other code of your query that you haven't pasted. Please check step-by-step to see where the error sits.