Forum Discussion
Loops with PowerQuery
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 !
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
4 Replies
Sky571 - Huh, I've done recursion in Power Query but not sure I've ever done loops.
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
