Getting option set data in Power BI from Dynamics 365

Are you getting a code such as 1000000001, 100000004, or 1000000006 from the option set field data in Power BI when querying Dynamics 365? That's because the value in Dynamics is being displayed, and the option set value is in the entity definition itself. The best way to get the data to Power BI is to use XRMtoolbox’s plugin, “XRMToolbox – The PowerBI Option-Set Assistant”.

After opening the organization, click on “Load Entities”.

Check the entity you are working on and check the option set fields you want to use in Power BI.

Then click on “Create records for selected option sets". Click Yes.

Now connect to a new data source on Power BI, ODATA. You will see gap_powerbioptionsetref.

After the data source is loaded, you can link the fields together and add slicers and filters on Power BI.

3,735 views1 comment