Excel Power Query dynamically inserting rows

[nryan]

Hi all,

I need to dynamically insert a row into a table based on a cell value. I'm able to insert the row, but I haven't figured out how to make it dynamic.
The row will always go above the row with the value "2270PBAKE" in the STAGE column.
I'm using the Table.InsertRows function. For the offset value of that function I put "5" which is correct now but won't always be correct.

This is the code I'm using, from the Power Query advanced editor:

NewRows = Table.InsertRows(#"Sorted Rows",5,{[STAGE="Skip pre-bake step 2270PBAKE when using Ormet 701", DESCRIPTION=null, Init=null,Special Notes=null]})

Here's what the table looks like:

Can I replace the offset value "5" in the Table.InsertRows function with a dynamic reference?

Thank you,
Nick

 

[GraH]

Hi, actually you are looking to find the position of 2270PBAKE in the list "Stage".

The formula for this is

= List.PositionOf(Table.Column(#"Changed Type","Stage"), "2270PBAKE")

In M you can refer to a previous step. Code could be something like:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Stage", type text}}),
    vPosition = List.PositionOf(Table.Column(#"Changed Type","Stage"), "2270PBAKE"),
    Custom1 = Table.InsertRows(#"Changed Type",vPosition,{[Stage="Skip pre-bake step 2270PBAKE when using Ormet 701", DESCRIPTION=null, Init=null,Special Notes=null]})
in
    Custom1

Notice the InsertRows steps refers to the previous steps #"Changed Type" and uses the calculation done in vPosition.

I'm not sure about the record reference. It's hardcoded, but do you need that to be dynamic too?

 

[nryan]

Hi GraH,

This works. Thank you for your help.
The record reference will not change, so hard coding it works for now.
-Nick

 

[GraH]

Thx for the feedback, glad I could help.