Forum Discussion
Convert DirectQuery Mode to Import
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"?
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.
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.
7 Replies
- AllisonKennedy
Community Champion
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.
AllisonKennedy - Thank you.. This is exactly what I needed.
- bcdobbs
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.
- AllisonKennedy
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.
- bcdobbs
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
