Forum Discussion

LauraB's avatar
LauraB
Regular Visitor
1 year ago
Solved

Combining duplicate rows, sum some values and keep others

I have a duplicate row combination issue that I haven't found the whole answer to, and I hope the brains of this community can come together and help me.    I am working with a table in Power Query...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    LauraB 

    you can try this

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY/LCsMgEEV/RVy34luzbFLBFNGi6aKE/P9vVBMthlIQ5t6Rw2HWFXKiGLxAxgWXeT6MB2N8zT7lMi/lhyBcfvA3b5cVCixpXlBBlchzNM4ZMIWbyyW/K5MIk0rV0mHNdsJ2m5Bo4JU7csGUHPTJ5sEUjbk3ijNEK7THwjDNh06VbHa9QQxjCr5xVCPdwCMXUlI5dLbJxjkt4WlNBM6YvLGxXMUJoqqdeJSObt5f+t+d2wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID Number" = _t, #"Account Number" = _t, Name = _t, Department = _t, #"Current Charges" = _t, #"Total Adjustments" = _t, #"Total Charges And Adjustments" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID Number", Int64.Type}, {"Account Number", Int64.Type}, {"Name", type text}, {"Department", type text}, {"Current Charges", type number}, {"Total Adjustments", Int64.Type}, {"Total Charges And Adjustments", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "New Account Number", each Table.Sort( Table.SelectRows(#"Added Index",(x)=>[ID Number]=x[ID Number]),{{"Index", Order.Descending}}){0}[Account Number]),
    Custom1 = Table.AddColumn(#"Added Custom", "New department", each Table.Sort( Table.SelectRows(#"Added Index",(x)=>[ID Number]=x[ID Number]),{{"Index", Order.Descending}}){0}[Department]),
    #"Grouped Rows" = Table.Group(Custom1, {"ID Number", "New Account Number", "New department"}, {{"charges", each List.Sum([Current Charges]), type nullable number}, {"adjustment", each List.Sum([Total Adjustments]), type nullable number}, {"total", each List.Sum([Total Charges And Adjustments]), type nullable number}})
    in
    #"Grouped Rows"

     

     

    pls see the attachment below

lbendlin's avatar
lbendlin
Super User
1 year ago

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY7bCsMgDEB/RaSPnXi/PK6dYIfo0O5hlP7/byw4Wix7SELCyUm2DUtmBB6xkEpqqE+f0FTeS6rQLCukQTBCNd7HDSuqOUy44kZBnXyMHs35HqFBELdBaEJZzx7eC/vzKk2cbKzRzl68Cc3F+8eJSkE4baiw0nXaGsD7QSVPNacT55ZY3nDNtevMcyhLXfMr+IKi9zAJpf0tGeGm3zgO/G9cnt+/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Account = _t, Name = _t, Dept = _t, charges = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"charges", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Name"}, {{"Account", each Table.Sort(_,{{"charges",Order.Descending}}){0}[Account]}, {"Dept", each Table.Sort(_,{{"charges",Order.Descending}}){0}[Dept]}, {"Current Charges", each List.Sum([charges]), type nullable text}})
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.