Referencing Cells in Power Query

[Oadr21]

I am having this trouble to reference cells in Power Query like in Excel. So I have the following table in Excel:

Where the formula for the Finish Qty column is the following:

=[@[Initial Qty]]-[@[Scrap Qty]]-[@[In Process]]

And the Initial Qty column is referencing the previous row value in the Finish Qty Column:

And only for the first cell in the Initial Qty, it picks up the sum from another table.

I use Power Apps to only collect the data from the In Process and the Scrap Qty columns, and the first row of the Initial Qty column, and Excel just do the calculations I've mentioned.

I am trying to change my data sources to Sharepoint Lists because I can dynamically change data sources in Power Apps using SP Lists, but this function is not available for Excel files.

So, I am trying to replicate what my excel file does in the power query, which I only have my inputs columns there so far:

Please let me know if this is possible,

[ronrsnfld · Accepted solution]

You can use the List.Generate function to accomplish the same result.

I started off with just your In Process and Scrap Qty columns.

And I stored the first "Initial Qty" value of 86 as a Parameter named Initial since you said you got that from someplace else.

Then, with this code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"In Process", Int64.Type}, {"Scrap Qty", Int64.Type}}),
    #"Initial/Finished" = List.Generate(
        ()=>[i=Initial, f=Initial - #"Changed Type"[In Process]{0} - #"Changed Type"[Scrap Qty]{0}, idx=0],
        each [idx] < Table.RowCount(#"Changed Type"),
        each [i=[f], f = [f]-#"Changed Type"[In Process]{[idx]+1} - #"Changed Type"[Scrap Qty]{[idx]+1}, idx=[idx]+1 ],
        each {[i],[f]}),
    
    #"Add Initial/Finished" = Table.FromColumns(
        Table.ToColumns(#"Changed Type") &
        {List.Transform(#"Initial/Finished", each _{0})} &
        {List.Transform(#"Initial/Finished", each _{1})},
        type table[In Process=Int64.Type, Scrap Qty=Int64.Type,Initial Qty=Int64.Type, Finish Qty=Int64.Type]),
    #"Reordered Columns" = Table.ReorderColumns(#"Add Initial/Finished",{"Initial Qty", "In Process", "Finish Qty", "Scrap Qty"})

in  
    #"Reordered Columns"

you obtain this result:

[Anonymous · Accepted solution]

Hi @Oadr21 ,

@ronrsnfld @lbendlin Thanks for your concern about this case!
And @Oadr21 another solution:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjJQ0lEyUIrViVZCsIyALEMwyxAhaIGi0BBNCy4x/CxjLGKmaOYZmxBjYCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"In Process" = _t, #"Scrap Qty" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"In Process", Int64.Type}, {"Scrap Qty", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Initial Qty", each let 
currentIndex = [Index]
in
if [Index] = 1 then 86 else 86 - List.Sum(
    Table.SelectRows(#"Added Index", each [Index] <= currentIndex)[In Process]) - List.Sum(
        Table.SelectRows(#"Added Index", each [Index] <= currentIndex)[Scrap Qty]
    )),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Finish Qty", each let 
   currentIndex = [Index],
   nextRow = Table.SelectRows(#"Added Custom", each [Index] = currentIndex +1)
in
   if Table.IsEmpty(nextRow) then 0 else Record.Field(nextRow{0}, "Initial Qty"))
in
    #"Added Custom1"

Output:

Best Regards,
Dino Tao
If this post helps, then please consider Accept all of the answers as the solution to help the other members find it more quickly.

[ronrsnfld · Accepted solution]

You can use the List.Generate function to accomplish the same result.

I started off with just your In Process and Scrap Qty columns.

And I stored the first "Initial Qty" value of 86 as a Parameter named Initial since you said you got that from someplace else.

Then, with this code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"In Process", Int64.Type}, {"Scrap Qty", Int64.Type}}),
    #"Initial/Finished" = List.Generate(
        ()=>[i=Initial, f=Initial - #"Changed Type"[In Process]{0} - #"Changed Type"[Scrap Qty]{0}, idx=0],
        each [idx] < Table.RowCount(#"Changed Type"),
        each [i=[f], f = [f]-#"Changed Type"[In Process]{[idx]+1} - #"Changed Type"[Scrap Qty]{[idx]+1}, idx=[idx]+1 ],
        each {[i],[f]}),
    
    #"Add Initial/Finished" = Table.FromColumns(
        Table.ToColumns(#"Changed Type") &
        {List.Transform(#"Initial/Finished", each _{0})} &
        {List.Transform(#"Initial/Finished", each _{1})},
        type table[In Process=Int64.Type, Scrap Qty=Int64.Type,Initial Qty=Int64.Type, Finish Qty=Int64.Type]),
    #"Reordered Columns" = Table.ReorderColumns(#"Add Initial/Finished",{"Initial Qty", "In Process", "Finish Qty", "Scrap Qty"})

in  
    #"Reordered Columns"

you obtain this result:

[lbendlin]

yes and no.

No, you cannot do that like in Excel. While you can reference other cells, you can only reference cells from previous steps, not from the step you are currently in.

Yes, you can implement your logic but you have to do it the Power Query way, via List.Generate or List.Accumulate.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...