Excel - Using SQL command on Power Query data

Question

Using Power Query, I pull in two reports from Salesforce using Power Query > Get external Data > From Online Services. I load them into tables in two different sheets, and then create a third worksheet and table using Data > Get external Data > From other sources > From MS Query > From Excel referencing the workbook itself.

Now I can change the SQL Command text under Connection Properties and reference the Salesforce reports by using the Worksheet they're in (e.g. [Sheet2$])

I am successfully able to change my Salesforce reports online and have the change show up in my Excel, and using SQL, I can do things with the data in a way that I'm familiar with.

My question: Can I do the same thing, without loading the Salesforce reports into worksheets? Without MS Query'ing the woorkbook itself?

Answer 1

I would uncheck the Enable Load option on your 2 Salesforce Queries (from the right-click menu). This will stop loading their data into an Excel table.

Then I would rebuild your 3rd Query, starting from a Reference to one of the Salesforce Queries (right-click menu). Then in that Query's steps, you can combine data from the other Salesforce Query by adding Merge or Append Steps.

The transformation steps available in the Power Query ribbon cover everything you can do using SQL and much more.