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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/246/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I had applied a function and calculated the rent for each Tent Type.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/246/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/246/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I choose to separate the data with a space, and call the column Tent Types.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/246/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I now have the Tent Types in one column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/246/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/246/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I need to group my data so that I have one row per Event / Date combination. I select ‘Group By’ from the Transform tab:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/246/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I group by Date and Event. For Tent Types, I will need the values from ‘All Rows’ and I sum the Rental Amount.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/246/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/246/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I am extracting Tent Types from the table, currently in the Tent Types column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/246/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/246/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I choose to ‘Extract Values’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/246/image12.png/a1537847463e660a31158c8032525438.jpg)
I opt to put the comma with a space (, ) back.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/246/image13.png/917da985be13220165c8d2823e95344f.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/246/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
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!