Please note javascript is required for full website functionality.
MVP

Blog

Power Query: Functional Query

3 April 2019

Welcome to our Power Query blog.  This week, I look at how to make a query more functional.

 

I have a list of expenses from my imaginary salespeople, which I have uploaded into the Power Query Editor, viz.

Some of the expenses are from May and some are from June.  Ideally, I want to be able to select those expenses which fall into a date period that I can define.  One way I can do this is to filter on the Date column:

By selecting the Date column and right-clicking, I have some ‘Date/Time Filters’ to select from.  I choose to use the ‘Between’ option.

When I specify the date selection, I have a list of options that I can use in reference to each date.  I have decided to pick dates greater than or equal to 1st June and less than or equal to 30th June.  As I have chosen all dates in June, I could just have filtered to select dates in month ‘June’ but later I will allow more flexibility in setting the date range.

The June expenses have been selected.  Next, I look at the M code generated in the Advanced Editor.  I am going to amend the code so that I can enter date parameters.

The generated M code is: 

let

    Source = Excel.CurrentWorkbook(){[Name="Expenses_Folder"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Expense Code", type text}, {"Amount", type number}, {"Name", type text}}),

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= #date(2015, 6, 1, 0, 0, 0) and [Date] <= #date(2015, 6, 30, 0, 0, 0))

in

    #"Filtered Rows"

In the #date sections, I am going to introduce two parameters: datefrom and dateto:

(datefrom, dateto) =>

let

    Source = Excel.CurrentWorkbook(){[Name="Expenses_Folder"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Expense Code", type text}, {"Amount", type number}, {"Name", type text}}),

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >= #date(Date.Year(datefrom), Date.Month(datefrom), Date.Day(datefrom)) and [Date] <= #date(Date.Year(dateto), Date.Month(dateto), Date.Day(dateto)))

in

    #"Filtered Rows"

When I execute this code, Power Query treats my query as a function.

I can enter my parameters, and invoke the function to see the results.  I’ll use 01/06/2015 and 30/06/2015.

I ‘Close & Load’ the query to save it to the workbook.

I can amend my query so that it’s more flexible for users by allowing the parameters to come from the worksheet – I’ll look at that in next week’s blog…

 

Come back next time for more ways to use Power Query!

Newsletter