The Power BI template for Dynamics 365 Sales from Microsoft is a great resource, but if you have changed the field options for default fields, it may be missing this data. In this blog, I'll show you how to fix that problem using the commonly tweaked field "Industry."
As I mentioned at the end of the last blog, now that you have the report in the Desktop version of Power BI, you can edit the report, its pages, all the visualizations, and even the data itself as much as you wish. The sky’s the limits, and it pays to approach this project collaboratively if multiple departments might be using your report.
To follow along with this process, you will need to complete the initial steps I outlined in my previous blog. Once that's complete, come back here. I'll be using the field "Industry" for this example, but this process works with several other default fields if you have added custom options to them in CRM.
If you have customized your industry options for accounts, you may notice right away that many of your opportunities show the industry as “(Blank)” in your Power BI visualizations, even though you have them set correctly in CRM. This discrepancy occurs because the template is only configured to handle the “out-of-the-box” industry lists Microsoft provides in Dynamics 365 Sales. To get your customized industry options to appear, you need to tweak one of the tables. Here’s how:
Now when you Close & Apply, your new industries will appear in your visualizations once the updates have processed.
If you're wondering how editing this separate table changes the industry options available for your Account entity (a completely separate table in Power BI), here's why it works. By default, CRM sends code values for the industry field to Power BI, not the display names. This means if you made any kind of visualization with the raw data, it would refer to industries by these numbers instead of their actual names, which would be quite confusing to the report reader.
The Microsoft template solves this for you. It includes a data transformation step that joins the Account table with the IndustryCodeOptions table we edited above. This process is very similar to Excel's VLOOKUP function, as it will read the code from the Account table, find the row with a matching code in the IndustryCodeOptions table, and then create a new column with the corresponding industry name for each row in the Account table. If it doesn't find a match, that column returns no data, so the visualizations will show the accounts as having "(Blank)" industry. By adding your custom options into the IndustryCodeOptions table, you make sure they are found when the tables are joined.
This same concept works if you have custom values in many other fields, such as:
To find all these options, browse the tables with "OptionSet" in their names under the Make Tables folder in your Power Query Editor.
I hope this brief tutorial has been helpful to you as you model your custom values in Power BI. In the next blog, I'll show you how you can add completely custom fields to your data so you can visualize the data that matters most to your business. See you then!