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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
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’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
Column1 and Column3 both contain header information, so I select them both by holding CTRL down and selecting ‘Merge Columns’ from the Transform tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I transpose my table, from the Transform tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
My data is nearly there – I promote the first row to headers and remove the empty columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I have my Single_Row query, which I load to an Excel worksheet.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
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’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I choose to ‘Append Queries’ (not ‘as New’).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I append Linking_Row.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I now have two rows, and since the ID is unique, I can right-click ID and ‘Remove Duplicates’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image12.png/a1537847463e660a31158c8032525438.jpg)
Now I am ready to enter more salespeople.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image13.png/917da985be13220165c8d2823e95344f.jpg)
I enter Mary’s data, and now I refresh Single_Row.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
Mary’s data has been added. I can add another salesperson to check.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
I update and refresh:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/174/image16.png/d082e3477129350b8a2a589156028e63.jpg)
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!