Loops with PowerQuery

[Sky571]

Hello,

I have some difficulty with loops in Power Query.

I would like to create a column in PowerQuery, named « Number » with this conditions :

• each time the occurrence of the headings « S21.G00.30.001 » appears, the value of  the headings « S21.G00.30.019 » should be indicated ;

• all the headings that are appearing before the headings « S21.G00.30.001 » have the number 0, even if the headings’ numbers are superior as the one as « S21.G00.30.001 (example : "S21.G00.44.001")

It would be great if you could help me with this

Here is the example file : https://drive.google.com/file/d/1E1zRuQqZIEbqix0BkDjPEiMnKQ4HGV-1/view?usp=sharing

Thanks !

[Greg_Deckler]

@Sky571 - Huh, I've done recursion in Power Query but not sure I've ever done loops. 

https://www.excelinppc.com/creating-loops-in-power-query

Looks complicated though. @ImkeF @edhans 

[Sky571]

Hi @Greg_Deckler 

Thank you for your answer, it also helped ! happy to see you here again 😉

Best xx

[HotChilli · Accepted solution]

Add an Index from 1 to the original table.  This helps with ordering and we'll use it to merge later.

Using 'Duplicate', make 2 copies of the table.

The first copy-> rename it to 'TableX19' and filter the Headings column to "S21.G00.30.019".  Add an Index column 'IndexLink' from 1.

The second copy -> rename it to 'TableX01' and filter the Headings column to "S21.G00.30.001". Add an Index column 'IndexLink' from 1.

Starting from TableX01, Merge the table with 'TableX19' on the IndexLink column using InnerJoin.

Expand the column with the table to only return the Values (which will be 2,4,6)

Select the original table. Using 'Merge as New', merge it with TableX01 on the Index column (not IndexLink) using Left Outer. 

Expand the column with table to return the Values(2,4,6).

You may need to reorder the table on Index at this point (I did) because the merge switches some ordering.

You should have something like this:

Do a 'Fill Down' on the last column.  Then use 'Replace Values' to replace the null values (should just be 2 left)  with 0.

Good luck 

[Sky571]

Hello @HotChilli,

Thank you so much for your answer, it was very detailed and clear. 

I have tested and it worked. I need to test another sub-recursion after, hopes it will work the same way.

Huge thanks !!

xx