Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How can I add one cell value to a running total using Index/match like function in Power Query

Please help, I am new to Power Query in Excel and trying to piece this together. I am pulling the data from a separate script, which refreshes regularly and when it refreshes it will add new lines th...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

Hi, I provided function query. If you don't know how to use it - read note below my post. (Copy my whol query! and change only Source part)

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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}

  • dufoq3's avatar
    dufoq3
    Icon for Community Champion rankCommunity Champion
    2 years ago

    You'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.