Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi friends,
I would to load data for 1 week only depend on current date!. I.e. Today is 12/14/2014 then my dataset will get data from 12/7/2016 to 12/14/2016 AND tomorrow, my dataset will load data from 12/8/2016 to 12/15/2016.
It means the dataset will get data for 1 week automatically, could anyone give me some advice on the way to do that in power query? i see i can filter date field but i just am able to fill in the dynamic date instead of using TODAY()-3 function!..
Best reagrds,
J.
Solved! Go to Solution.
Hi @MichaelJackpbi,
In Query Editor, Choose your date column -> click filter icon in top right of header of column -> choose Date Filters -> Custom Filter -> Fill in This quarter and Last quarter with OR operator as below pictures:
(Every actions have been done by UI - love it )
Back to your speed question, depends on your query(or amount of data) the loading time will be fast or slow. That means it will take more time when loading data for quarter than week. And i hope you have good index stategy in your sources. it's one important factor.
Hi @MichaelJackpbi, it all depends on where your source data is coming from?
If it is a database you can do it as part of your source query.
If the data is coming from a file, then yes you will need to bring all the data in first and then filter it afterwards on your date column. You can do this in a few ways, but we would need more details in terms of how your data looks to place the dynamic filter.
@GilbertQ THank for fast response!
Ya, i have a database contains few column like ID, Product,Code, OrderDAte,Qty....
I would create a dataset to only update for 1 week before from Now! i means today and can be able to refresh data everyday as following that rule.
My database is oracle 12C, i try to wirte the query to filter conditions in custom window but i see the speed to load data very slow than using choose tables
Regards,
J.
Hi @MichaelJackpbi, I would suggest doing it at the source, in doing so if your data is large your Oracle database is much more efficient at only giving you the data you require.
Also then you would have to load all the data into Power BI and then filter it out, which will take longer to load the data.
@GilbertQ you mean i have load all of data from database to power bi then filter as my rules! there are 65m rows in database and i just need to get 1 week only- around 2-3m..
Hi @MichaelJackpbi,
Some basic queries of Power Query(M Language) in Query Editor will be executed in your data source and return result without loading all data.
E.g: I will filter FullDateAlternateKey in this month by power query as below pictures
So if you have large/big data, you should consider to filter data in Query Edit before using DAX. Please try your case with Date.IsInCurrentWeek method for getting data of this week.
To understand more about this solution, you could search about Query Folding concept
And if you get the issue when loading all data with Importing mode, you could switch to DirectQuery Mode (it will load data based on user's behaviors)
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyen
Hi @tringuyenminh92 It souunds great ! But i still confuse about the data loading speed, may the edit effect to the speed? in case i would keep the rows for 2 Quarters - current Quater & Last Quater, how to do that?
Thanks,
J.
Hi @MichaelJackpbi,
In Query Editor, Choose your date column -> click filter icon in top right of header of column -> choose Date Filters -> Custom Filter -> Fill in This quarter and Last quarter with OR operator as below pictures:
(Every actions have been done by UI - love it )
Back to your speed question, depends on your query(or amount of data) the loading time will be fast or slow. That means it will take more time when loading data for quarter than week. And i hope you have good index stategy in your sources. it's one important factor.
Hi @MichaelJackpbi, yes that would be correct.
The reason is that if your query from Oracle, said give me all the data, it would then give you the 65m rows. Which would then be loaded into Power BI. And then you would then apply the filter to filter out the rows to leave you with 2-3m rows.
So it would be best to write the Oracle TSQL to only get the last weeks worth of data.
Something like this after doing some Googling
where adddate >= next_day(trunc(sysdate), 'MONDAY') - 14 and adddate < next_day(trunc(sysdate), 'MONDAY') - 7
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |