Please note javascript is required for full website functionality.
MVP

Blog

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!

Newsletter