Power Query: Part Time

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 [0].

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!