Forum Discussion
How can I add one cell value to a running total using Index/match like function in Power Query
Hi Anonymous
Based on your information, you can create a blank query and put the following code to advanced editor in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjQwMFDSUXIEYlMI0wJIxurAZVxAQpZgprkpikwgEJtBZMygeqBG+IBMs4DImKLIuAIxRIshqoQvxGaIYWAJcwjPHWoIWAeKjB8QIzsYKuyJ8IoxSCYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Goal = _t, Consumer = _t, CW_Amount = _t, #"Running Total Current" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Goal", Int64.Type}, {"Consumer", type text}, {"CW_Amount", Int64.Type}, {"Running Total Current", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Goal"}, {{"Data", each _, type table [Goal=nullable number, Consumer=nullable text, CW_Amount=nullable number, Running Total Current=nullable number]}, {"LastCWAmount", each List.Last([CW_Amount]), type nullable number}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Consumer", "CW_Amount", "Running Total Current"}, {"Consumer", "CW_Amount", "Running Total Current"}), #"Added Custom" = Table.AddColumn(#"Expanded Data", "Running Total", each [LastCWAmount]+[Running Total Current]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"LastCWAmount"}) in #"Removed Columns"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Okay, here is your Code copied over and only slightly updated for naming convention match:
= let
fnRunningTotal =
(myTable as table)=>
[
// _Detail = GroupedRows{[#"Outs_Goal"=7000]}[All],
_Detail = myTable,
_BufferedAmount = List.Buffer(_Detail[INV]),
_lg = List.Generate(
()=> [ x = List.Count(_BufferedAmount)-1, y = _BufferedAmount{x} + List.Last(_Detail[CW_OUTS]) ],
each [x] >= 0,
each [ x = [x]-1, y = [y] + _BufferedAmount{x} ],
each [y]
),
_ToTable = Table.FromColumns(Table.ToColumns(_Detail) & {List.Reverse(_lg)}, Value.Type(_Detail & #table(type table[Running Total=number], {})))
][_ToTable],
Source = Source,
ChangedType = Table.TransformColumnTypes(Source,{{"Outs_Goal", Int64.Type}, {"INV", type number}, {"CW_OUTS", type number}}),
GroupedRows = Table.Group(ChangedType, {"Outs_Goal"}, {{"fn", fnRunningTotal, type table}}),
Combined = Table.Combine(GroupedRows[fn])
in
Combined
Still getting an error of DataFromat.Error: We couldn't covert to Number.
Details:
[Table}
Community ChampionYou've changed column names but when I change names too - the query is working. Make a screenshot of your Source table and also the error.