Power BI & Salesforce “Query is too complicated…” Error Resolved

By Jason Hall on December 19, 2019

I have a confession to make. I love Power BI, but I don’t always do nice things to external APIs when I use it. A good example is when I need a quick set of data visuals from Salesforce. It is so easy just to pop open the “Get Data” dialog, pull in entire objects, and then hammer out what I need.

Data pros will instantly understand that when I do this on something wide, let’s say the “Accounts” object, I’m going to get a lot more than I need in terms of columns which this wastes resources. I won’t go into the debate over right and wrong, but I will say that I think this is justified in a quick one-off situation.

Salesforce knows there are lots of people out there doing this, because I recently ran into a little snag. Salesforce admins add tons of custom and/or calculated columns to these objects, and I recently received the following when I tried to bring data into a new Power BI Desktop session.

Busted! The Salesforce API, being the good steward that it should be, is going to force me to stop abusing it.

I need to limit the columns coming back to only what is necessary, which is frankly just a best practice I should follow in general, but, again, sometimes you just need something fast and temporary.

How do I do this? The query editor of course! There is an option to use the “Advanced Editor” in the Power BI query editor.

Here, I can see and modify the M code used to retrieve the data from Salesforce. I wasn’t (and still am not) extremely familiar with this language, so I turned to a blog post from Chris Webb for an understanding of what I was seeing.


    Source = Salesforce.Data(),
    Case1 = Source{[Name="Case"]}[Data]

Translates to: “Get me everything from an object named Case.”

I needed to change it to: “Get me case number and case owner from an object named case.”

I turned to Microsoft docs to figure that out, and ended up finding syntax for choosing only the columns I want.

I changed the M code to this:

    Source = Salesforce.Data(),
    Cases = Source{[Name="Case"]}[Data],
    LessColumns = Table.SelectColumns(Cases, { "CaseNumber", "Case_Owner__c" })

This was precisely what I needed—just the case number and owner, no more and no less. The error goes away, I can load my case data, and then build my chart(s).

In summary, Power BI external connectors are powerful. They allow us to pull from many sources with just a few clicks in a GUI. At the same time, while we may not all need to become masters at defining and understanding what is going on behind those buttons, it will eventually become necessary to at least break the surface on it. When it happens to you, there are lots of great resources available from both the community and Microsoft to help you learn about and then accomplish whatever it is you are trying to do in Power BI.

Jason Hall

Jason Hall

Jason has worked in technology for over 20 years. He joined SentryOne in 2006 having held positions in network administration, database administration, and software engineering. During his tenure at SentryOne, Jason has served as senior software developer and founded both Client Services and Product Management. His diverse background with relevant technologies made him the perfect choice to build out both of these functions. As SentryOne experienced explosive growth, Jason returned to lead SentryOne Client Services, where he ensures that SentryOne customers receive the best possible end to end experience in the ever-changing world of database performance and productivity.

Related Posts