How to stop Power Query reading all external data?

themissingelf
Posts: 3
Joined: Mon Feb 06, 2017 10:51 pm

How to stop Power Query reading all external data?

Postby themissingelf » Tue Feb 07, 2017 12:09 am

Hi,

I have a workbook which reports on 5 years of data. One output is a report showing this years performance vs. last year and therefore only requires two of the years. However, the query takes an age to run and I notice it seems to be trawling through ALL the external data for all years (txt files). Is there a more efficient way of approaching this?

For example, should I create a two year table of data and then access it from a separate workbook to complete the remaining queries to turn it into the final dataset?

Thanks

MattAllington
Posts: 818
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: How to stop Power Query reading all external data?

Postby MattAllington » Tue Feb 07, 2017 4:24 am

can you describe how it is currently designed. Maybe even post the code here.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

themissingelf
Posts: 3
Joined: Mon Feb 06, 2017 10:51 pm

Re: How to stop Power Query reading all external data?

Postby themissingelf » Tue Feb 07, 2017 8:34 pm

Hi,

I cannot post the code now but I can describe. I have one query that pulls in and cleans up the text files for the five years of data. A separate query then references the first and then immediately filters to the two years I am interested in working with. The same (second) query then goes on to perform a number of other steps etc. It seems that each of these steps takes some time to process because the query appears to be trawling back through all the original text files even though I've already filtered.

Thanks

MattAllington
Posts: 818
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: How to stop Power Query reading all external data?

Postby MattAllington » Wed Feb 08, 2017 9:26 am

Power Query uses query folding where available to push work back to the source, but this only happens for a database. If your source is txt files, it has no choice but to load and process all the data even if you only want a subset. One option would be to change your query to filter out the txt files you don't need as one of the first steps. Then it will only load a smaller set of data.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

themissingelf
Posts: 3
Joined: Mon Feb 06, 2017 10:51 pm

Re: How to stop Power Query reading all external data?

Postby themissingelf » Thu Feb 09, 2017 11:27 pm

Thanks for explaining. It makes sense. I thought I was being smart loading the data using a single query then filtering for the second query; however, it seems as though I'd do better to go back to source and apply the filter on the files themselves.


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest