Hi all,
I am quite new with PowerQuery and need your help to identify the mistake of my PowerQuery Codes. The case is about doing Running Total.
I have a dataset / table containing column "Material", "Week" and "SUMIFS". This Column "SUMIFS" is already the result of another step, which you will see in my codes. Column "RunningTotal" is the intended result. The idea is to do cumulative iteration per Material.
| Material | Week | SUMIFS | RunningTotal |
| A | 1 | 100 | 100 |
| A | 2 | 10 | 110 |
| A | 3 | 30 | 140 |
| B | 1 | 70 | 70 |
| B | 2 | 20 | 90 |
| B | 3 | 5 | 95 |
| C | 1 | 80 | 80 |
| C | 2 | 0 | 80 |
| C | 3 | 1 | 81 |
Here is my PowerQuery Code
let
Source = Excel.Workbook(File.Contents("C:\Users\wibowoal\Desktop\Test.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Material", type text}, {"Field", type text}, {"Week", Int64.Type}, {"Qty", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Material", "Week"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Added Custom" = Table.AddColumn(#"Removed Duplicates", "SUMIFS", each List.Sum(
Table.SelectRows(
DataSource,
(InnerTable) => InnerTable[Material] = [Material] and
InnerTable[Week] = [Week]
)[Final Quantity]
)),
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type),
#"GroupMaterial" = Table.Group(#"Added Index", {"Material"}, {
{"RunningTotal", each List.Buffer(List.Generate(
() => [RunningTotal=0, PrevWeek=0, Index=0],
each [Index] <= List.Max([Index]),
each [
RunningTotal= [RunningTotal] + #"Added Index"[SUMIFS],
PrevWeek=[Week],
Index=[Index]+1
],
each [RunningTotal]
)), type list}
}),
#"Expanded" = Table.ExpandListColumn(#"GroupMaterial", "RunningTotal")
in
#"Expanded"
However, I got the error message as follows: "Expression.Error: We cannot convert the value 0 to type List. Details: Value = 0, Type=[Type]. PowerQuery starts to show this error during #"GroupMaterial" step.
Really appreciate the help! 🙂
