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)
3. Filtered based on "TRUE" value for the Custom Column
It has limited application and is niche. But thought I'd share.
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]
It has limited application and is niche. But thought I'd share.