Forum Discussion

gregmhoffmann's avatar
gregmhoffmann
Frequent Visitor
7 years ago
Solved

Remove Duplicate rows based on max value of column query editor

I'm running into an issue where I have duplicate UIDs creating a many to many relationship and duplicating rows in a table visual.  Is there a way in the query editor to remove duplicates based on a ...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi gregmhoffmann ,

    You could refer to my query below.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYzBDQAhCAR74e1DQM/zKW0Y+2/jWDXkPhMyS2ZOGoOSg50s6sy0UmhxihaMf40/LRXj1mZ+miFSnxaRqxFpb4/I1Yj0zCeyPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t, Account = _t, AccountID = _t, #"Number of Records" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Account", type text}, {"AccountID", Int64.Type}, {"Number of Records", Int64.Type}}),
     #"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"Count", each Table.FirstN(Table.Sort(_,{{"AccountID", Order.Descending}}),1), type any }}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Account", "AccountID", "Number of Records"}, {"Account", "AccountID", "Number of Records"})
in
     #"Expanded Count"

    Here is the output.

    More details, you could refer to my attachment.

    Best  Regards,

    Cherry

     

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
7 years ago

Hi gregmhoffmann ,

You could refer to my query below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYzBDQAhCAR74e1DQM/zKW0Y+2/jWDXkPhMyS2ZOGoOSg50s6sy0UmhxihaMf40/LRXj1mZ+miFSnxaRqxFpb4/I1Yj0zCeyPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t, Account = _t, AccountID = _t, #"Number of Records" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Account", type text}, {"AccountID", Int64.Type}, {"Number of Records", Int64.Type}}),
     #"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"Count", each Table.FirstN(Table.Sort(_,{{"AccountID", Order.Descending}}),1), type any }}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Account", "AccountID", "Number of Records"}, {"Account", "AccountID", "Number of Records"})
in
     #"Expanded Count"

Here is the output.

More details, you could refer to my attachment.

Best  Regards,

Cherry