evaluating field usage in excel / power query / power bi
Summary
The article explains how to leverage Power Query's M language and the Table.Profile function to analyze Salesforce object field usage, including statistics like null count and distinct values. It shows how to connect to Salesforce data, profile it directly or through Power BI dataflows to improve performance and reduce API calls. It also covers advanced techniques to dynamically profile data by record type using grouping and custom columns in Power Query. Salesforce teams can use this approach to audit field usage, optimize data quality, and understand data profiles without additional apps or complex tooling.
Takeaways
- Use Power Query's Table.Profile to get detailed field usage stats from Salesforce objects.
- Leverage Power BI dataflows to run profiling in the cloud and reduce Salesforce API calls.
- Replace empty strings with null in dataflows to get accurate null counts in profiles.
- Add custom aggregate functions like max length within Table.Profile for richer insights.
- Group data by record type and profile each group dynamically using Power Query custom columns.
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, or any other field on the object, you can do that via the power query UI prior to adding the line of code to run the profile. It also is possible to do this dynamically by record type, but is more complex, so that will be a separate article. 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.