Forum Discussion
Loops with PowerQuery
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 - 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
