Return Entire Row for Distinct Column

[Anonymous]

Person        | Date      | Department A Total | Department B Total |

John Smith | January  |                2               |                  1              |

John Smith | February|                 1              |                  0              |

John Smith | February|                 1              |                  2              |

I want to create a "New Table" from an existing query where it returns all columns except distinct for values if Person and Date, and in case of duplicate, chose the row which has higher value of "Department B Total"

So I want to return following

Person        | Date      | Department A Total | Department B Total |

John Smith | January  |                2               |                  1              |

John Smith | February|                 1              |                  2              |

[Anonymous · Accepted solution]

In Power Query:

• Reference Original Table
• Groupby Person and Date, Aggregate by All Rows
• Add a custom column to sort the subtable on Dept B Total Descending
• Add a custom column to keep the first row of the sorted table from the step above
• Remove all other columns except the last one
• Expand out

PBIX file:

https://1drv.ms/u/s!Amqd8ArUSwDS0nzUfb-Fggj9lw0g

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyAvOzSzJUNJR8krMK00sqgSyjIDYUClWB1XeLTWpCKrAEIgNCCkwUoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t, Date = _t, DepartmentATotal = _t, DepartmentBTotal = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Date", type text}, {"DepartmentATotal", Int64.Type}, {"DepartmentBTotal", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Person", "Date"}, {{"B Total", each List.Max([DepartmentBTotal]), type number}, {"All rows", each _, type table [Person=text, Date=text, DepartmentATotal=number, DepartmentBTotal=number]}}),
    #"Expanded All rows" = Table.ExpandTableColumn(#"Grouped Rows", "All rows", {"DepartmentATotal", "DepartmentBTotal"}, {"DepartmentATotal", "DepartmentBTotal"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All rows", "Custom", each [B Total]=[DepartmentBTotal]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"DepartmentBTotal", "Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Person", "Date", "DepartmentATotal", "B Total"})
in
    #"Reordered Columns"

[Anonymous · Accepted solution]

In Power Query:

• Reference Original Table
• Groupby Person and Date, Aggregate by All Rows
• Add a custom column to sort the subtable on Dept B Total Descending
• Add a custom column to keep the first row of the sorted table from the step above
• Remove all other columns except the last one
• Expand out

PBIX file:

https://1drv.ms/u/s!Amqd8ArUSwDS0nzUfb-Fggj9lw0g

[Anonymous]

Thanks, but the link is not working!

[Anonymous]

It says 

This item might not exist or is no longer available

This item might have been deleted, expired, or you might not have permission to view it. Contact the owner of this item for more information.

[Anonymous]

@Anonymous 

Sorry about that, this link should work:

https://1drv.ms/u/s!Amqd8ArUSwDS0wVmLDvo_4LHSNt7