Power Query: Today’s Calendar
22 May 2019
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:
= Number.From(Date.From(DateTime.LocalNow()) - [Date])
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!