Problem with data loading

[Anonymous]

Hi community!

I'm having a problem with data loading. In preparing report that needs to show how many new joiners are in my company in 3 past months. In order to do that I've created 3 queries like below.

I'm using 5 files here - each of them is around 25mb so total is ~125mb. All queries take the latest file for each month.

The previous_minus_1_month works rather smoothly as there is no reference so no special calculations are needed here.

But in the query previous_month I check if employee number from that query is in the previous_minus_1_month  query. I use something like that 

if List.Contains(previous_minus_1_month[GGID_LeadPractice], [GGID_LeadPractice]) then 0 else 1)

It took sometime to load but then, when I tried to filter it (value 1) in the right down corrner, the PBI was downloading huge amount of data.

Finaly, I've cancelled that and tried to apply changes but I've encountered the same problem.

How is that possible that it downloads over 3GB od data when the actual datasets has 120mb? 

Is there a way to workaround it somehow? Maybe set up some cache options?

Thanks in advance!

[v-yanjiang-msft]

Hi @Anonymous ,

Here's a similar thread, please see if it helps: Power Query File Suddenly Exploded to very large file size...why?

Additionally, you could check the following to improve performance of power query.

Speeding up power query

Table.Buffer for cashing intermediate query results or how workaround Unnecessary Queries Issue

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi kalyj!

Thanks for replay. Unfortunately, the solution in the first link doesn't really apply as this is a dynamic report and I will be using different files from an online folder. But I will dig into the advices you also provided. For now, I can see that it works better when I merge queries and use join-like-workaround instead of List.Contains. 

Thank you!