I am not sure that I completely understood session 4.19. I thought after seeing that session, that if I built a query I liked, then "Reference" would take the steps I had manually completed on that query and apply them to another dataset in the same layout/format without me having to redo all the steps myself. However, when I select the existing query and choose Reference, it appears that it simply duplicates the existing query with the one change that there is now only "Source" listed under applied steps. It does not take those applied steps and perform them on the new dataset.
Do I understand that correctly? If so, is there a way to do what I actually want to do?
Thank you,
Benedikte
Hi Benedikte,
You can think of referencing another query much like referencing a cell in Excel that contains an Excel formula. e.g. let's say in cell C2 you reference cell B2 (i.e. cell C2 contains this formula =B2). B2 contains a complex formula that evaluates to 2500. The result you see in C2 is 2500, but you don't see the complicated formula contained in cell B2, just the result of it. If the result in cell B2 changes then you'll see that new result in C2.
Referencing a query in Power Query does the same thing. i.e. Query 1 contains a load of steps to get and clean some data. You then create a new Query (2) which references Query 1, all you see is the data in the final step of Query 1. You can then add further transformations to Query 2 and if anything in Query 1 changes or gets updated then this will flow through to Query 2.
So, if you want to re-use a query but apply it to different data then you have a few options:
1. Save a copy of the file and edit the M code in the advanced editor to point to a different source file.
2. Copy the M code from the Advanced Editor and paste it into a blank query in a new file and edit the Source line of code to pick up the new source data.
3. See sessions 7.03 and 7.04 for automating this process if you want to repeat this process every week/month when you get new source data.
Hope that all makes sense, but shout if you have further questions.
Mynda
