I have a table that shows data like the following:
masterProduct relatedProducts
324111 3058816
324111 3058825
324111 3058898
324111 3058900
324111 3058904
355646 1805213
355646 1805222
355646 1805237
355646 2527465
355646 2759115
355646 2971681
I want to add an additional column that will show an index for each masterProduct so I end up with a table that looks as follows:
masterProduct relatedProducts Index
324111 3058816 1
324111 3058825 2
324111 3058898 3
324111 3058900 4
324111 3058904 5
355646 1805213 1
355646 1805222 2
355646 1805237 3
355646 2527465 4
355646 2759115 5
355646 2971681 6
How can I achieve this using PowerQuery?
Hi Baxbax,
Does this have to be done in Power Query? It's easy with a PivotTable - see attached example. In Power Query it's much more complicated and requires a custom function etc.
Mynda
Hi Mynda,
I actually found a similar solution by just adding a formula column to my table. I was trying to use PowerQuery was because the actual reason I was adding the index was so that I could pivot the data so that I got one row for each masterProduct and each relatedProduct appeared in columns. I thought that if it was achievable via PowerQuery then I could kill two birds with one stone so to speak.
Thanks for taking your time to respond. I have attached my file with the final solution if it is of any interest.
Regards
Bax
Thanks for sharing, Bax.
Power Query doesn't easily reference rows above/below like you can in Excel. It requires custom functions and the like, so this type of calculation is easier done in Excel, but possible in Power Query if it makes sense to do it there because it's required for a subsequent step.
Mynda
