Power Query: The Delete Dilemma
22 July 2020
Welcome to our Power Query blog. This week. I look at data from a sheet made up of a number of tables, with a random amount of data that needs to be excluded.
Mary, one of my imaginary salespeople, has sent in a slightly unusual report of the tents available from a particular supplier.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I want to put the tent, supplier and report date into a table, and delete the lines at the top (there will be an unknown number of these). I begin by extracting my data to Power Query, using ‘From Table’ on the ‘Get & Transform’ section of the Data tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
The ‘Create Table’ dialog box tries to default to the table I happen to be in, but I need to change this to include all of my data; instead of using ‘From Table’, I will adopt another approach.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I select ‘From Workbook’ in the dropdown from the ‘From File’ option on the ‘New Query’ options, and select my current workbook.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I select the sheet.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I can see that all my data has been included; I can now choose to ‘Transform Data’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
Power Query has automated some steps, but I don’t want to include most of them, so I delete all steps after the ‘Navigation’ step.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I need to remove the top rows, but I want to avoid specifying how many of these rows there are. I plan to delete all rows above the date value. I begin by adding an index from the ‘Add Column’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
Next, I create a duplicate column of Column3 (which contains the report date value) on the ‘Add Column’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
In order to get rid of all the data apart from the date, I convert Column3 – Copy to Data Type ‘Date’. This may be done from the ‘Transform’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
This means that apart from the date, the data values are now either null or Error. If I right-click my column, I can replace the errors with null values.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
This gives me null values, and I can now fill down by right-clicking, choosing to ‘Fill’ and then choosing to ‘Fill Down’. The rows above the date do not matter as I want to delete them.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image12.png/a1537847463e660a31158c8032525438.jpg)
I need to delete the null value rows, so I filter and choose values that are not null.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image13.png/917da985be13220165c8d2823e95344f.jpg)
This gives me the rows with a date value.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
I have the date column. Now, I want to get the supplier into a column. I create another duplicate of Column3:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
I convert the Data Type on my new column to ‘Date’ because I want to isolate the value with the date.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image16.png/d082e3477129350b8a2a589156028e63.jpg)
I create a new conditional column from the ‘Add Column’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
If the value is not null, then I want to put the supplier from Column2 into my new column, which I call Supplier. The values with errors will remain as errors.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image18.png/61935f470d8b9f21b7b7683ffca40895.jpg)
I can now right-click on Supplier and replace the errors with null.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image19.png/06909e3fbfc8faf87de7555c0b99e9d3.jpg)
Having done this, I can fill down my supplier, as before.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image20.png/173ef895b62ce391674d271b99c4d178.jpg)
I have one more row to remove, and I can do this by filtering on Column1 to remove null values.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image21.png/bc31e9359fd69b42bc104de457d8f9f8.jpg)
I have one final row to delete, but this time it has values in all columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image22.png/059b7a0f2b398df09f943b811a5b3126.jpg)
The data I need in the top row has already been moved to columns. I could just delete the top row, but instead I choose to identify it by creating a new conditional column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image23.png/287595eacf557e9bf11ea5283a715ce1.jpg)
I choose to create a new column which will indicate a row should be deleted if the value in Supplier matches the value in Column2.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image24.png/c20c20624705a0da5ec0bb162b062eab.jpg)
I can then filter the Delete Me column to remove everything that isn’t null.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image25.png/d1f447eaaef4f12aad7eba0759ff10c1.jpg)
I now consider the column headings, but first I want to remove Column3 – Copy1 because the errors might cause problems. I also remove my other temporary columns at the same time.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image26.png/c4be63c37c731d35ebe71055df24b261.jpg)
I can now promote the first row to headers from the Transform tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image27.png/a28c3d756a6785e68daad8f55186300c.jpg)
I just need to tweak the supplier and date headings.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image28.png/b8e752cc441321a35196a6559f63ba46.jpg)
My final step is to ‘Close & Load’ my data into Excel.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image29.png/d0df8c90b5a7daae74aba80275bf8ae6.jpg)
If I go back and alter the data by adding some extra rows to be deleted, I can check my transformation.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image30.png/d9437dec8e14b8851a396191db87cdf9.jpg)
I create another line at the top and an extra space before the tent data. Then, I refresh the query.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image31.png/4fd048e53251cf2154cc69062df1ab50.jpg)
The extra lines appear in the source.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/190/image32.png/553a29ddfdd6e90ad72affac596b9f33.jpg)
The data is still transformed correctly.
Come back next time for more ways to use Power Query!