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.
This:
let
Source = Salesforce.Data(),
Case1 = Source{[Name="Case"]}[Data]
in
Case1
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:
let
Source = Salesforce.Data(),
Cases = Source{[Name="Case"]}[Data],
LessColumns = Table.SelectColumns(Cases, { "CaseNumber", "Case_Owner__c" })
in
LessColumns
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.