cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Optimizing import and refresh

I'm wondering if there is any better (more computationally efficient) way to filter my imported data.

 

I have a transaction log table where each row has a foreign key that relates the row to an operational domain in my organization. Typically on a report I only want bring rows that relate to say, one of the 10 particular domains that belong to the division the report is about.

 

I filter the rows by first importing the Domain table and filtering it down to the 10 domains I want on the report. Then in the transaction table, I filter each row using each List.Contains(List.Distinct(Domain[id]), [domain_id])). So basically, for each row in the transaction table, we check if the domain foreign key is in the list of domain ids from the Domain table.

 

This gets the desired result but import seems a bit slow. The reports need to be refreshed once per day. Is there possibly some more efficient approach?

 

 

1 ACCEPTED SOLUTION
Super User II
Super User II

Hi @Anonymous 

Yes, merging both tables with JoinKind.Inner (instead of the default: LeftOuter) should return the results much faster.

 

But if you're importing from a foldable source and your Domain tables doesn't come from the same server, you might have to adjust your technique as soon as your Domain-table gets larger than 200 rows: https://www.thebiccountant.com/2017/07/20/sql-query-folding-bug-still-alive-sucking-powerbi-powerquery-excel/ 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
Super User II
Super User II

Hi @Anonymous 

Yes, merging both tables with JoinKind.Inner (instead of the default: LeftOuter) should return the results much faster.

 

But if you're importing from a foldable source and your Domain tables doesn't come from the same server, you might have to adjust your technique as soon as your Domain-table gets larger than 200 rows: https://www.thebiccountant.com/2017/07/20/sql-query-folding-bug-still-alive-sucking-powerbi-powerquery-excel/ 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Anonymous
Not applicable

The reason I'm using the Domain table is that I can construct it in such a way that new domains are automatically included.

 

So far I haven't looked at all into what happens with the folded query, so I will try to study the folded query to see if it is suboptimal.

Hi @Anonymous ,

we might have a misunderstanding here.

I'm not recommending against the Domain table.

Just that you should use it differently.

If you use it with the InnerJoin, it will only be adressed once and not per every row of your fact table.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Super User IV
Super User IV

What happens if you skip the lookup table and just pick the [id] values that you want directly in the import table? Column header, drop down uncheck the values you don't want?


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors