Power Query: Part Time
27 January 2021
Welcome to our Power Query blog. This week, I look at adding rows to existing data in order to present a complete picture.
I have some data for parts for my imaginary tent business.
It shows me how many parts of a particular type that were required, but there are no rows if the parts required were zero. I want to have a row for each part and date combination, even if the required quantity was zero.
To do this, I begin by uploading my data to Power Query. I use ‘From Table’ on the ‘Get & Transform’ section of the Data tab.
I accept the defaults for the ‘Create Table’ dialog.
I want to view my data in date order, so I sort on the Date column.
I can do this by selecting the Date column and using the icon next to the title. I choose to ‘Sort Ascending’.
I then do the same thing for Part Number.
I would like to generate rows for the part numbers to show when there was no demand. There are a number of ways to do this, but I will create a query to link to. This new query will be a matrix, linking all dates to all part numbers in my query.
I create a new query just for the Date column.
I then choose to remove duplicates, so that I have a list of unique dates.
I go back to the original query and do the same for Part Number.
I want to combine my two new queries, so I convert Part Number to a table. I will add the data from the list query Date to my Part Number table.
On the ‘Add Column’ tab, I choose to add a custom column. In the formula box for this column, I enter the list query Date.
This creates a new column where each value is a list. If I click in the space next to each list, I can see the contents. I need to expand the list.
I choose to expand to new rows, as my aim is to create a matrix where each date links to each part number.
I have my matrix, and for clarity, I rename my columns and change the data type of Date to date / time. I can link this matrix query to my original query, to fill in the values that I have for Quantity Required.
From the Home tab, I select ‘Merge Queries’. I choose to merge this with my original query and link on Part Number and Date. I choose a left outer join.
I need to expand the new column; I will only need the quantity column.
I choose to only extract Quantity Required, and to keep that column name.
I want to show all the null values as zeroes.
I right click on Quantity Required and choose to ‘Replace Values’. I opt to replace null with zero .
I reorder my columns and sort on Date and then Part Number. I have my data in the required format at long last!
Come back next time for more ways to use Power Query!