3

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?

2
  • Have you tried not loading the source data into Excel and simply making a connection in Power Query?
    – Sudachi
    Nov 7, 2017 at 23:44
  • @Sudachi If I don't load the data into Excel, and just make the connection, I don't know how to access the data in my SQL command text. Nov 8, 2017 at 15:55

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.