Excel Power Query dynamically inserting rows

nryan

Board Regular
Joined
Apr 3, 2015
Messages
61
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:
VBA Code:
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:
1590176570062.png


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

Thank you,
Nick
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

GraH

Active Member
Joined
Mar 22, 2020
Messages
445
Office Version
365, 2016
Platform
Windows
Hi, actually you are looking to find the position of 2270PBAKE in the list "Stage".

The formula for this is
Code:
= List.PositionOf(Table.Column(#"Changed Type","Stage"), "2270PBAKE")
In M you can refer to a previous step. Code could be something like:
Code:
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

Board Regular
Joined
Apr 3, 2015
Messages
61
Hi GraH,

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

GraH

Active Member
Joined
Mar 22, 2020
Messages
445
Office Version
365, 2016
Platform
Windows
Thx for the feedback, glad I could help.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,833
Messages
5,446,771
Members
405,415
Latest member
Noodnutt

This Week's Hot Topics

Top