Forum Discussion
Dynamic Filter on a Date/Time Column?
in Power Query you need to use the old school #duration(0,8,0,0)
DateTime.LocalNow()-duration(0,8,0,0)
You're right, I should have said "rows" or "data".
Does the rest of my post above make sense as far as what I'm trying to do? Basically load all data into Power BI, then "pre-filter" that data within Power BI so only the rows connected to the timestamps from the last 8 hours appear.
yes, that's a reasonable approach. "Aggregate/Filter your data as soon as possible but not sooner"
Let me put it another way because I realize that sounds confusing. I'll use Tableau as an example as that's what I'm more familiar with.
In Tableau, there's two types of filters -- Data Source filters and Report filters.
Report filters are the ones everyone is familiar with where users can open up a report, apply the filters they want so the report changes dynamically. They can clear all filters if they want all the data stored in the report to appear in the report. Every single BI application like Tableau, Power BI, QlikView, etc. has report filters.
Data source filters allow you to "clean up" data before it gets loaded into a report at all. If you filter out data at the data source level, that data is not going to be accessible at all in the report because it doesn't exist.
I'll give an example. Say you have a report that's fed purely from a single Excel spreadsheet with a ton of data and you want to clear out all data that is older than the past year using a date/time column. Of course, you could edit the Excel document itself and clean it up that way. But using Tableau, you can also connect the raw Excel document to Tableau, then use Tableau's data source filter feature to do the same clean-up within Tableau. The latter is what I'm wondering if Power BI has an equivalent to.
yes, you do that part in Power Query as part of the data prep.
Ok, I'm starting to figure this out. Currently I've been playing with the query below to try to get this to work:
= Table.SelectRows(Source, each ([TimeStamp_field] = null or [TimeStamp_field] > DateTime.LocalNow()-8/24))
But there's something wrong with the second half of that where I'm trying to subtract 8 hours from the current time. I'm getting this error message:
Any idea how I can fix my code above?