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:
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!