evaluating field usage in power query / power bi

RIP Field Trip…a great app it was. there are other apps now that do the same thing, and free ones to boot. I’m sure they are great but my new job has some hoops to jump through to install them so i tested out the idea i had years ago to do this using power bee eye.

The M language used in power query makes it remarkably easy to do this. It has a Table.Profile function, which takes a table as an input, and returns the usage, like magic.

You can use the salesforce integration to pull an object, and add one line of code in advanced editor, and you have it.

If you want to filter by record type, etc, you can do that via the power query UI, prior to adding the line of code. I dont think its possible to do this in power bi itself, but gonna try when i have the time.

Note that if you run this on your desktop and have an object with a lot of fields and a lot of records, your machine may get rather hot. Fortunately with a fairly cheap power BI Pro license, you can create a dataflow (gen 1) which can do all this in msft the cloud, and then you can analyze in power bi or you can reference the dataflow in as a data source in excel.

I will write this up in more detail, but top level - when you pull in a salesforce object in power query via data - get date - from online services - from salesforce objects, you can go into advanced editor and the M code will look like

let
  Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion = 48]),
  #"Navigation 1" = Source{[Name = "Contact"]}[Data]
in
  #"Navigation 1"

and all you need to add is

profiledata = Table.Profile(#"Navigation 1")

so it becomes

let
  Source = Salesforce.Data("https://login.salesforce.com/", [ApiVersion = 48]),
  #"Navigation 1" = Source{[Name = "Contact"]}[Data],
  profiledata = Table.Profile(#"Navigation 1")
in
  profiledata

note the commas etc as M is a funny language. when you save the system will add a few additional lines but you dont need to worry about that…

reference this in power bi (which you need to do on power bi desktop, bc msft nonsense) or in power query, and you will see the magic