Please note javascript is required for full website functionality.
MVP

Blog

Power Query: Row by Row

1 April 2020

Welcome to our Power Query blog.  This week, I look at how to add a table of data to an existing query.

 

I need to create a table for my imaginary salespeople, but I only get one record at a time.

To set my table up, first I create a query for this data.  I extract my data to Power Query using the ‘From Table’ option in the ‘Get & Transform’ section of the Data tab.

I need to get my data into a neat format with each piece of data in a separate column.  I can see that I don’t need all my columns, so I remove Column2 by right-clicking that column, and choosing ‘Remove’.

Column1 and Column3 both contain header information, so I select them both by holding CTRL down and selecting ‘Merge Columns’ from the Transform tab.

I change ‘Salesperson DataName’ to ‘Name’ by creating a conditional column in the ‘Add Column’ tab.  I can then delete Merged.  I reorder my columns so that Custom appears first, since it has the headings.

I transpose my table, from the Transform tab.

My data is nearly there – I promote the first row to headers and remove the empty columns. 

I have my Single_Row query, which I load to an Excel worksheet.

Next, I extract this table to a new query which I call Linking_Row, which I save as connection only.  At this point Single_Row and Linking_Row contain the same data.  With this in mind, I go back to ‘Single Row’:

I choose to ‘Append Queries’ (not ‘as New’). 

I append Linking_Row.

I now have two rows, and since the ID is unique, I can right-click ID and ‘Remove Duplicates’.

Now I am ready to enter more salespeople.

I enter Mary’s data, and now I refresh Single_Row.

Mary’s data has been added.  I can add another salesperson to check.

I update and refresh:

All my salespeople appear, and I can keep adding using the entry table I created.

 

Come back next time for more ways to use Power Query!

Newsletter