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.
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.
Solved! Go to Solution.
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"
@Anonymous This should work as a DAX table
Latest Table = SUMMARIZE ( 'Table', 'Table'[Sales Order], "Latest Version", CALCULATE ( MAX ( 'Table'[Version] ), ALLEXCEPT ( 'Table', 'Table'[Sales Order] ), FILTER ( 'Table', 'Table'[Version] = MAX ( 'Table'[Version] ) ) ), "Latest Date", CALCULATE ( LASTDATE ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Sales Order] ), FILTER ( 'Table', 'Table'[Version] = MAX ( 'Table'[Version] ) ) ) )
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
thanks, made the change but still getting "token comma expcted" errror
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I've checked all brackets now. Token EopF expcted is the new error
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
78 | |
64 | |
56 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |