Today I was using power query to import external data in a CSV file and then do some transformations on the data. I decided I wanted to import my data as a connection only first then perform the transformations in a separate query.
I had My Sales Data connection only query ready to use in another query. I hadn’t really noticed before, but there’s actually no command in the ribbon to get data from a query. I could have loaded my first query to a table and then queried that table, but I wanted to keep the first query as a connection only so using the From Table/Range command was not possible.
I diligently searched all the commands in the Get Data menu from the Get & Transform Data section of the Data tab in the ribbon, but could not find a From Query command.
It turns out, the way to query a query is to create a Blank Query and then reference your query as a source.
- Press the Get Data button found in the Get & Transform Data section of the Data tab.
- Select From Other Sources in the menu.
- Select Blank Query in the menu.
After creating a blank query, the query editor will open and appear blank. We can now add our My Sales Data connection only query as the source data for our blank query. Do this by typing =#”My Sales Data” into the query editor formula bar and pressing Enter.
A preview of the data from My Sales Data will load into the query editor and you can then begin your transformations as you wish.
👉 Find out more about our Advanced Formulas course!
How do turn this into a dynamic parameter? Where I can change the “My Sales Data” to some other query. I need the syntex to invoke the custom function here.
Wow, that’s awesome.
It is going to completely change the way I gonna use PQ.
Thank you so much for such an amazing tip.
No problem. You can also right click on a query in the query list of the editor and select reference to reference a query.