Inside Power Query reference queries for Power BI and Excel

Chris Webb joins Patrick to look at what happens when you use reference queries for Power Query. This applies to both Power BI and Excel. Chris also looks at how change the behavior to make things a bit faster.

Connect with Chris Webb:

********
LET'S CONNECT!
********

***Gear***

[alexjames]

This video, single handledly, smashed my excel grind, from 2 hours for 6 data sets - down to 20 seconds with 1000 data sets!

[scramiro]

Great video! I think this is one of the videos that advanced developers should watch. For instance, I tended to use the Table.Buffer for referencing several queries, but now I got it! Thanks for this great video!

[dhawalpmehta]

Yes it is more than what i have thought, it gave me a food for the weekend to digest. Thanks a lot guys for this eye opening video. Thanks Chris Webb.

[markward]

Just found this video and starting to explore powerquery. I think this will help reduce some of my looong load times. So thank you will need to run some tests now :)

[Vaizard]

Excellent video, made it very easy to understand something I've googled more than a few times. Thank you

[rudisoft]

Thanks for this really important information to cut down dataset gathering time!

[pmsocho]

Thanks for the video! More episodes with Chris please! :)

[BillSzysz]

Thanks Chris and Patrick. Very interesting topic!!!

[kyleparsons]

Great video! I'm going to have to watch this a few more times

[jeroendekk]

Excellent video, I do think this somehow should be made simpler. At least easier to see what happens. (In this case it is easy because of flow, but some sources are far less clear).

[pawewrona]

Patrick surprised Chris with question about Current File settings :)

[bhlgxwn]

Thanks for the great video!
I think I missed expression of Reference Query 1, 2 and 3. Is it just =#"Call Web Service" ?

[abhisoulstormer]

Are these settings automatically loaded into the PowerBI premium workspace online service too? or is there additional setting to do that or does that happen accordingly?

[AnthonyNomakeo]

Great video! Would any of these "tricks" allow you to bypass the error "Formula.Firewall: Query references other queries, so it may not directly access a data source"? I've received this error when trying to use the response from one of my web service queries as the basis for another query. Also, any good sources on how to utilize Microsoft Flow with PowerBI.

[marcioevangelista]

❤ Amazing. Thank you very much. My project ran from 3 hours to 2 minutes. You guys are great. Now the big question, where should I use Table.Buffer? I indeed remove it all. Anyway, Awesome video.

[RecoveringHermit]

Great video! If you replicate those queries in Dataflows instead (and you have premium) is it true that the number of calls can be reduced to 1? I think that's how DFs work, and I'm trying to justify needing premium licences. thank you!

[yornav]

Thanks for this very interesting video. I asked about this recently via FB Messenger and received an adequate answer fairly quickly. By disabling parallel loading, it improved the query speed as I had a lot of tables in my model that were referenced from a single data source table. But still I don’t understand why there is no mechanism that uses some kind of internal querying. So a mechanism where you have one table that is retrieved from the data source and where the reference tables query that one table. Would it be a feature that can be added in the future or are there concrete reasons why one wouldn’t want that at all?

[tmb]

I've been grappling with this for a while from an Excel perspective, and everything I've read and seen suggests that there is actually no explicit way to ensure your data source is only queried once. The workaround I've been using is to load the 'intermediate' step to a worksheet, then use the worksheet as the source for subsequent queries, but that obviously leaves you with a superfluous worksheet (and is no good if your data set contains more than 1m rows). It's a bit unsatisfactory, really. I wish there was some way to reverse the evaluation so that it went top-down rather than bottom-up. It already knows the dependencies, after all.

Makes me just want to use Python for everything on the ETL side.

[PSchaff]

Hey guys! Thanks for the really great video! Helped me a lot in understanding how Power Query an M handle query excecution.I thought ;)

Because I have a question, after my Report has not updated as expected:

1. I have two queries: 2 folders of Excel tables, that are separately queried and transormed.
2. Then I have a third query in which I purely append the two queries with "Table.Combine(...)".

I would expect, that with all the settings that you just showed, it would query the two folders first and then very quickly append the two results.

What happens is this:

Both folders are loaded and then in the third query all the files are loaded again :( Very slow and very inefficient.

[DreeKun]

So, if i understood right, Table.Buffer():
- is not useful between multiple query executions, because it can't share its output that way;
- is only useful when you want to reuse the data multiple times in the same query;
- gets executed everytime a query that implements it is called, instead of sharing its result
Is this correct?