Power Query: Rental Wrangles Receipts - Part 1
11 August 2021
Welcome to our Power Query blog. This week, I look at an example where I need to transform my data before I can apply a function.
Last time, I was looking at how to extract the correct rental information for an event, where only one tent was being rented. This time, I am looking at larger events where I have list of tents required for each event.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
With such large groups involved, I’ve had to return to 2019 for this example! I start by extracting my data to Power Query using the ‘From Table/Range’ option on the ‘Get & Transform Data’ section of the ‘Data’ tab. Since the process for the rental data is the same as last week, I can skip to the function created.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
The function is ready to be applied to the event data. I ‘Close & Load To’ and create my function and the supporting table as ‘Connection Only’ queries. I go back to the Excel Worksheet and extract my data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I take the defaults and create my query, which I call Event Data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I need to divide the Tents Rented column in two different ways. First, I need to create a row for each tent type, and second, I need to separate the quantity and tent type into different columns. I will be using the ‘Split Column’ functionality for both tasks. I can access ‘Split Column’ either by right-clicking my column or from the Transform and Home tabs.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I choose to split ‘By Delimiter’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
I choose to ‘Split into Rows’ in the ‘Advanced options’ section and opt for a Custom delimiter.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
The delimiter I am using is comma followed by a space (, ). This means there will be no space before the numbers.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I now have rows for each tent type with the quantities. It’s always a good idea to check that all steps generated are needed. In this case, the Split Column step has automatically been followed with a Changed Type step. This would be useful if I had created new columns, but since I have created rows instead, this step is not needed, and I can delete it.
I split the column again:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
This time I am going to split ‘By Digit to Non-Digit’, to extract the tent quantities.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
Ironically, I don’t have an automatically generated Changed Type step this time! I rename Tents rented.1 to Quantity and Tents Rented.2 to Tent Type. I also change the data type of Quantity to be a whole number, which I can do from the Transform tab. Since I am going to use Tent Type as a parameter, I also remove any spaces. I can do this by using Trim from the ‘Format Options’ dropdown on the Transform tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
This will remove any leading or trailing spaces.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image12.png/a1537847463e660a31158c8032525438.jpg)
The next step is to create a Custom Function Column by ‘Invoking Custom Function’ from the ‘Add Column’ tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image13.png/917da985be13220165c8d2823e95344f.jpg)
I call the new column Rental Rate and invoke fx_Rental, passing Tent Type and Date as the parameter values.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
Before I continue, I change the data type of Rental Rate to be a currency.
Now I just need to perform a simple multiplication. I choose to do this from the ‘Add Column’ tab to keep the data for each tent. I select Quantity and Rental Rate (with the CTRL key held down).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
Choosing Multiply from the dropdown list of Standard mathematical functions will create a new column, which is a product of Quantity and Rental Rate.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image16.png/d082e3477129350b8a2a589156028e63.jpg)
I rename Multiplication to Rental Amount.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/245/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
That is where I will leave it this week. Next time, I will recombine my data so that I have one line for each event.
Come back next time for more ways to use Power Query!