Power Query: Next (Row) Again Please

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:

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.

My data has headers, so I accept the defaults.

I can open the ‘Queries’ pane to the left of the screenshot.

I right click on my query to see the options available: I choose the ‘Duplicate’ option.

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.

I choose to start my index ‘From 1’.

I create an index on my duplicate query, but this time I start from zero (0).

I choose to ‘Merge Queries as New’ from the ‘Merge Queries’ section on the ‘Home’ tab.

I select to merge my queries on Index.

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)’.

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.

I do not want to ‘Use original column name as prefix’.

I filter Customer to remove the null values.

This gives me rows that have the item code in Item Code/Description and the description in Item Code/Description.1.

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.

I close and load this query.

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!