Power Query: Today’s Calendar

Welcome to our Power Query blog. Today, I am going to look at how to use the calendar table to filter on the current day.

I looked at how to create a calendar table a long time ago in Power Query: Calendar Creation – Preparing for Dates and Power Query: Calendar Creation – Going for Dates. More recently, I looked at an example where I had a folder of expense files that had a date embedded in them, and I selected the ones that matched ‘today’ in Power Query: Files for Today. The column I will add to my calendar table today provides another way to solve that issue, as I will show next time…

In order to see the query I used to generate this calendar, I can choose to ‘Show Queries’ from the ‘Get & Transform’ section on the ‘Data’ tab.

I can edit my query by double-clicking on it, by right-clicking and selecting ‘Edit’ or by hovering over it and choosing ‘EDIT’ from the pop-up sample data screen.

I choose to add a ‘Custom Column’ from the ‘Add Column’ tab.

The M code I have used is:

which gets the date from the current date and time, and then subtracts the Date value.  The result is converted to a number.  Date.From works in the local timezone, so this formula will work dynamically to give the difference between Date and the local date. 

Next time I will use my Calendar query to filter expense files on today’s date.

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