Power Query: Next (Row) Again Please
19 February 2020
Welcome to our Power Query blog. This week, I look at another solution to last week’s problem referencing other rows.
John, my reliable imaginary salesperson, has been filling in data again. Referring to last week’s blog, I have some information on items purchased by customers in December:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
He has decided to combine the item and the description in the same column, so I need to move the description into a separate column and remove the extra rows.
This week, I look at a solution, where I use a duplicate query.
I extract my data to Power Query using the ‘From Table’ option on the ‘Get & Transform’ section on the ‘Data’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
My data has headers, so I accept the defaults.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I can open the ‘Queries’ pane to the left of the screenshot.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I right click on my query to see the options available: I choose the ‘Duplicate’ option.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I have a query ‘Table1 (3)’ that is the duplicate of my original query ‘Table1 (2)’. I go back to ‘Table1 (2)’, and choose to create an index from the ‘Add Column’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
I choose to start my index ‘From 1’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I create an index on my duplicate query, but this time I start from zero (0).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I choose to ‘Merge Queries as New’ from the ‘Merge Queries’ section on the ‘Home’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I select to merge my queries on Index.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I take the default join, ‘Left Outer’, which will take all of the data from ‘Table 1 (2)’ and combine (merge) it with matching data from ‘Table 1 (3)’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I have my original table ‘Table1 (2)’ with a new column, which contains the linked rows from ‘Table1 (3)’. I expand the data from ‘Table1 (3)’ by clicking the icon next to the column name.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image12.png/a1537847463e660a31158c8032525438.jpg)
I do not want to ‘Use original column name as prefix’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image13.png/917da985be13220165c8d2823e95344f.jpg)
I filter Customer to remove the null values.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
This gives me rows that have the item code in Item Code/Description and the description in Item Code/Description.1.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
I can now rename Item Code/Description and Item Code/Description.1 to Item Code and Description respectively. I also remove the other columns that I no longer need, including the indices.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image16.png/d082e3477129350b8a2a589156028e63.jpg)
I close and load this query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/168/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
I have the same results I achieved for last week’s method. Next time, I will look at a more complex solution for extracted data where the item data is not split into two rows for every customer.
Come back next time for more ways to use Power Query!