Power Query: Refined Rental Wrangles
4 August 2021
Welcome to our Power Query blog. This week, I look at creating a function to get data from a table.
Last week, I was working with some data on tent rentals:
I also had a Table of one day events, where I wanted to include the correct rental amount.
Last time, I expanded the tent rental data so that I had an entry for each date. This time, I am going to create a function to extract the correct rental amount. I will extract my rental data to Power Query, by using ‘From Table/Range’ in the ‘Get & Transform Data’ section of the Data tab. I will call it Rental Step, as it is the table that I will be creating a function from.
There are different ways to create a function. I am going to follow a method which involves creating as little manual M code as possible! I am going to create a parameter to filter Date and a parameter to filter Tent Type before I continue. I can do this from ‘Manage Parameters’ on the Home tab:
My date parameter is called P_Rental_Date:
I have deliberately chosen to default to a date which I know is later than my rental data. This will make it obvious that one of my later steps is required.
The next parameter is P_Tent_Type:
I choose a Tent Type value as the default.
Having created my parameters, I go back to Rental Step and apply a filter to Date.
Note that this screen can be accessed by choosing any filter, and then ‘is before or equal to’ can be selected from the dropdown. I choose to filter dates before or equal to P_Rental_Date, which will currently select them all because of the late date I chose for the default.
I then apply a filter to Tent Type.
As this is currently set to “Large”, I will get all rows for “Large” with any date.
This is why I chose to default P_Rental_Date to a date later than my data. I can see that, to get the latest rental information for “Large” at the top, I need to sort the dates in descending order.
Now I need to calculate the Rental Rate, so I only want that column.
Finally, I drill down into the latest Rental Rate so that I only have the latest value. I can do this by right-clicking on the cell and selecting ‘Drill Down’.
Finally, I can convert my query to a function. As I am going for the route of least M code, I do this by right-clicking on my finished query.
I am then prompted for the name of my function:
I create fx_Rental:
All the M code has been created for me, and even better, I can change my function by amending the steps in Rental Step rather than using the Advance Editor here. In fact, if I try to call the Advanced Editor, I am warned not to break the link:
Now I can go back to the events data and add a new column from the ‘Add Column’ tab.
By choosing ‘Invoke Custom Function’ I can call my new function and pass Event Date and Tent Type as the parameters.
I have the same results as last time.
Come back next time for more ways to use Power Query!