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 |
CodePudding user response:
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
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"
CodePudding user response:
Try this.
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"