Optimizing import and refresh

[Anonymous]

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?

[ImkeF · Accepted solution]

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/ 

[Anonymous]

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.

[ImkeF]

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.

[Greg_Deckler]

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?