How Can One Sum Dynamic Column Values in Powerquery Efficiently

val 0
Viewed 52

I have been learning and thesame time carrying out a project using powerquery. I am trapped on adding column values.Some of the column values contain text.I intend to sum in each record of my table, all values with integer type.However, there is a challenge . When i add up the column values with the interger type,i get a wrong answer.Secondly, this column headers are dynamic. How do i sum effectively dynamic column headers in powerquery

Example: My Challenge: When i sum the column with interger type like this [MEC101]+[THER305] i get a null values on some records and i dont know why?

When wrapped the sum using list.sum function, it partially works ,buh whenever,one of the column headers is missing, it gives a wrong answer.I want a suituation, when a column header is missing, it will ignore the missing column headers and sum the values from the available column headers. Thank you.

ID MEC101 MEC-GRADE THER305 THER305-GRADE TOTAL
1002 70 A 40 D
1003 50 C 60 B
1004 60 B 30 F

EXPECTED RESULTS 1:

ID MEC101 MEC-GRADE THER305 THER305-GRADE TOTAL
1002 70 A 40 D 110
1003 50 C 60 B 110
1004 60 B 30 F 90

EXPECTED RESULTS 2:

ID MEC101 MEC-GRADE TOTAL
1002 70 A 70
1003 50 C 50
1004 60 B 60
powerquery excel
2 Answers

try this which will sum the numeric columns, excluding 1st column

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
totals = Table.AddColumn(Source, "Sum", each  List.Sum(List.Transform(List.RemoveNulls(List.RemoveFirstN(Record.FieldValues(_),1)), each if Value.Is(_,type number) then _ else 0)))
in totals

EDIT try this which will sum the numeric columns, excluding 1st and 2nd columns

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
totals = Table.AddColumn(Source, "Sum", each  List.Sum(List.Transform(List.RemoveNulls(List.RemoveFirstN(Record.FieldValues(_),2)), each if Value.Is(_,type number) then _ else 0)))
in totals

or, using unpivot, grouping and merging:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Value.Is([Value],type number) then [Value] else null),
#"Grouped Rows" = Table.Group(#"Added Custom", {"ID"}, {{"Total", each List.Sum([Custom]), type nullable number}}),
#"Merged Queries" = Table.NestedJoin(Source, {"ID"}, #"Grouped Rows", {"ID"}, "Table1", JoinKind.LeftOuter),
#"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Total"}, {"Total"})
in  #"Expanded Table1"
horseyride0

Try this.

enter image description here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFJQ0lEyNwCRjiDCBMx0UVCK1QHLG4O4pmBBZxBhBmY6weVNUAR1lIzBTDegfCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, MEC101 = _t, #"MEC-GRADE" = _t, THER305 = _t, #"THER305-GRADE" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"MEC101", Int64.Type}, {"MEC-GRADE", type text}, {"THER305", Int64.Type}, {"THER305-GRADE", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
       listA =  Record.ToList(Record.RemoveFields(_,"ID")),
       result = List.Accumulate(listA, 0,(state, current) => if Value.Is(current, Number.Type) then state + current else state)
    in result)
in
    #"Added Custom"
userjpYe10
Related