Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have around 10 Tables which I configured with Direct Query because I didn't know enough about PowerBI. These tables all have records which have years worth of data.
I would like convert them to IMPORT but at the same time, only import the last YEARS worth of records.
There is an option to convert the TABLES to import but I cannot find a way to limit what gets imported. I know I can convert to IMPORT and then use an Advanced Query to limit the data I use but it doesn't stop importing everything.
The only option, I have been able to find is to delete the Table and add it as a data source and then include an Advanced. SQL query.
Anyone know of a way to limit the data that is imported after changing the Storage Mode to "Import"?
Solved! Go to Solution.
@hatchda You can filter a Direct Query, so click 'Transform Data' to open Power Query, and add a filter to your tables for in the last or current year. Then apply changes, then swap to import.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
One note @bcdobbs with using a SQL statement is that you will lose any ability of query folding after that, so unless you plan to do everything in a SQL statement, might actually be better to take your first suggestion of filtering.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
If you click transform data while still in direct query mode you can apply filters in Power Query to limit the data. Or if you need to limit it via SQL query do the same by clicking the cog next to "Source"
and enter the SQL query there.
Once done if you switch to import mode it will just load that subset.
One note @bcdobbs with using a SQL statement is that you will lose any ability of query folding after that, so unless you plan to do everything in a SQL statement, might actually be better to take your first suggestion of filtering.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Cheers @AllisonKennedy, should have made that much clearer. I added it in, in case there was no easy way to filter by year without a join. That said one of my favourite recent discoveries is the following which lets you enable folding with native queries: https://m.youtube.com/watch?v=8hjdOCni_ZY
@bcdobbs Thanks for the link! My mind is blown! I really need to prioritise my Query Folding learning journey, I'm clearly waaay behind the game. Have had this on my watchlist for ages: https://www.youtube.com/watch?v=9sV3hIn8VTY&list=PLKW7XPyNDgRCorKNS1bfZoAO3YSIAVz3N
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy that's a great looking series. Learnt something already; hadn't realised you could get a modern profiler inside azure data studio!! Thank you!
@hatchda You can filter a Direct Query, so click 'Transform Data' to open Power Query, and add a filter to your tables for in the last or current year. Then apply changes, then swap to import.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
AllisonKennedy - Thank you.. This is exactly what I needed.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
85 | |
85 | |
67 | |
49 |
User | Count |
---|---|
133 | |
113 | |
100 | |
68 | |
67 |