Forum Discussion

christiannobis's avatar
christiannobis
New Member
1 year ago
Solved

Summarize Multiple Columns

Hi,   I am trying to summarize multiple columns in the Origianl Table below.  I would like to add up all the Amounts and summarize by the Charge Des so that I can summarize in a table similar to on...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
1 year ago

Hi,

Using this Power Query code, transform your data into a 3 column table

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromRows(List.Split(List.Skip(Record.ToList(_),2),2),{"Description","Amount"}))[[#"Order #"],[Custom]],
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Description", "Amount"}, {"Description", "Amount"}),
    Custom1 = Table.SelectRows(#"Expanded Custom.1", each [Description]<>null and [Amount]<>null),
    #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Order #", type text}, {"Description", type text}, {"Amount", type number}})
in
    #"Changed Type"

Yu should now be able to build your desired visual.

Hope this helps.