Power Query: Rental Wrangles Receipts - Part 2

Welcome to our Power Query blog. This week, I continue with my example where I need to transform my data after I applied a function.

Last time, I was looking at how to extract the correct rental information for an event, where I had a list of tents required for each event:

I had applied a function and calculated the rent for each Tent Type.

To get all the information for the event in one row, I need to recombine the Tent Type with the Quantity, and combine the Tent Types for each event into one row.

I start by using ‘Merge Columns’ on the Transform tab, while I have Quantity and Tent Type selected. I select Quantity first and then hold down CTRL while I select Tent Type.

I choose to separate the data with a space, and call the column Tent Types.

I now have the Tent Types in one column.

The next step is to combine my rows. In this context, Rental Rate will not make sense (since I have different tent types for one event), so I delete this column.

I need to group my data so that I have one row per Event / Date combination. I select ‘Group By’ from the Transform tab:

I group by Date and Event. For Tent Types, I will need the values from ‘All Rows’ and I sum the Rental Amount.

I currently have a table of data in each field of the Tent Types column. I need to extract the Tent Types data from the table. To do this, I add a new Custom Column from the ‘Add Column’ tab.

I am extracting Tent Types from the table, currently in the Tent Types column.

Now I have the Tent Types in a list in each field of Custom. I delete the original Tent Types column, and rename Custom to Tent Types. I use the extract icon.

I choose to ‘Extract Values’:

I opt to put the comma with a space (, ) back.

I have all the data I want; I just need to tidy up by re-ordering my columns and setting the data type of Rental Amount to currency.

I now have Event Data with the original format of one row per event, and I have added the rental information.

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