Power Query: Rental Wrangles Receipts - Part 2
18 August 2021
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!