Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hatchda
Frequent Visitor

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

 

 

 

2 ACCEPTED SOLUTIONS
AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

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

View solution in original post

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.


Please @mention me in your reply if you want a response.

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

View solution in original post

7 REPLIES 7
bcdobbs
Super User
Super User

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" 

bcdobbs_0-1646077426262.png

 

and enter the SQL query there.

 

Once done if you switch to import mode it will just load that subset.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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.


Please @mention me in your reply if you want a response.

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



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

@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 


Please @mention me in your reply if you want a response.

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!



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

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. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors