• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

PowerQuery - Filter for Today-1 using M

Chihiro

Excel Ninja
Problem:
Today, I was scratching my head to find ways to filter PowerQuery data dynamically for yesterday for a connection to SQL.

Normally, you can create a view in SQL itself with "Where" clause to dynamically filter data. But for this database, I did not have sufficient privilege. Also, you can directly write optional SQL statement in Connection to get yesterday's data only.

However, my boss wanted loaded table to have filtered data for yesterday, but for OLAP cube to have complete set of data.

This posed bit of challenge. I couldn't find way to dynamically filter for yesterday's date based on date column.

Solution:
1. Create Custom Column
2. Used following M formula to check TRUE/FALSE for yesterday's date (my date column is named ReportDate)
Code:
= Date.AddDays(DateTime.Date(DateTime.LocalNow()), -1) = [ReportDate]
3. Filtered based on "TRUE" value for the Custom Column

It has limited application and is niche. But thought I'd share.
 
Top