Power Query: Refined Rental Wrangles

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!