Excel Power Query Sum Another Table Column Base on Current Table Column Cell Value-CodePudding

I have a table which have a different claim category hit by an individual. This table known as MergeAll:

Another table known as Query1 and the Category column is there and I need help for the Sum column. The figure in Sum column in the image is manually populated for illustration purpose.

For example row one of Category is Check-in On Friday/saturday, want the code able to go MergeAll table Check-in On Friday/saturday column to sum up and populate at the Sum column.

Any idea?

CodePudding user response：

In the MergeAll table,

add column, index column

right click index column, unpivot other columns

right click attribute column, group by, new column name: Total, Operation:Sum, Column:Value. OK

If you want you could then merge into the other table, but really thats already giving you what you want.

Sample code

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Attribute"}, {{"Total", each List.Sum([Value]), type number}})
in #"Grouped Rows"