Convert DirectQuery Mode to Import

[hatchda]

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"?

[AllisonKennedy · Accepted 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. 

[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 · Accepted solution]

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

[AllisonKennedy]

@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 

[bcdobbs]

@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!

[AllisonKennedy · Accepted 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. 

[hatchda]

AllisonKennedy - Thank you.. This is exactly what I needed.