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

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
1 year ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ID Number"}, {{"AN", each Table.SelectRows(_[[Account Number],[Total Current Charges]],each [Total Current Charges]>0)},{"DN", each Text.Combine([Name],"")},{"D", each Table.SelectRows(_[[Department],[Total Current Charges]],each [Total Current Charges]>0)},{"TCC", each List.Sum([Total Current Charges]), type number}, {"TA", each List.Sum([Total Adjustments]), type number}, {"TCCA", each List.Sum([Total Charges And Adjustments]), type number}}),
    #"Expanded AN" = Table.ExpandTableColumn(#"Grouped Rows", "AN", {"Account Number"}, {"Account Number"}),
    #"Expanded D" = Table.ExpandTableColumn(#"Expanded AN", "D", {"Department"}, {"Department"})
in
    #"Expanded D"

Hope this helps.