Skip to main content
Post a question

Notifications

Announcements

No record found.

Picklist (optionset) label issue on Power BI and Dynamics 365 integration

Considering the scenario that Power BI is retrieving info from Dynamics 365 (online), when picklist (optionset) fields are fetched, only the value for each option is retrieved and label is not. This behavior is considered by design. There are a bunch of ways to workaround it.

On this article we will approach a method used during a project inside the Kepler University program, Kigali, Rwanda, which belongs to the Microsoft MySkills4Afrika volunteer program.

Main focus of the project: empower the University with Power BI in order to get valuable insights from Dynamics 365/OnerWorld Student Information System (SIS) solution.

 

In summary:

1)     Identify the reference (value: label) for each picklist (optionset) entry (D365)

2)     Create a table to store the optionset’s references (Power BI)

3)     Merge queries (Power BI)

 

 

(1) Identify the reference value: label for each picklist (optionset) entry

To retrieve this info, as suggestion, you can use the Metadata Browser solution.

Download and installation details here.

After install it, go to Settings > Solutions > MetadataBrowser solution > Open Entity Metadata Browser

7167.1.png

Select Contact as entity and localize the optionset field gendercode from Attributes section (tip: use Text Search at the top).

7382.2.png

From the right pane side, navigate to OptionSet option and click on the Get Options button.

7180.3.png

 

 

(2) Create a query to store the optionset’s references

Open Power BI Desktop (pre-requirement that Contact table is already synchronized) and navigate to Edit Queries (ribbon bar) > Edit Queries.

1680.4.png

 

Create a new blank query.

3580.5.png

 

Insert the query definition, such as:

=#table({"value","label"},{{1,"Male"},{2,"Female"}})

=#table({"value","label"},{{<id>,"<text>"},{<id>,"<text>"},...,{<id-n>,"<text-n>"}})

Note: if you would like to consider null entries, use a reference such as {null, “Blank”}.

4201.6.png

2086.7.png

 

As suggestion, rename the recent query for a more meaningful name.

7356.8.png

 

 

(3) Merge queries (Power BI)

Now, we need merge the original query (contacts) and new query (contacts-gendercodelabel), focusing on the common column between them (value).

From right pane, select the original table (contacts) and navigate to Home ribbon > Merge Queries > Merge Queries.

7206.9.png

 

Select the new table (contacts-gendercodelabel).

4405.10.png

 

Make sure to select the common column between the tables (gendercode/value) and keep the Join Kind option as Left Outer (all from first, matching from second).

5756.11.png

8407.12.png

 

Now, the column has been added to original table (contact) with the proper value:label entries.

4062.13.png

Select the new column added to the query and check the label.

2746.14.png

0743.15.png

 

As suggestion, rename the recent column for a more meaningful name.

1200.16.png

 

At end, we have a column with the proper labels to use into the visualizations.

3414.17.png

 

 

 

Referencing articles

https://docs.microsoft.com/pt-br/dynamics365/customer-engagement/analytics/customize-power-bi-content-packs#add-a-custom-option-set-field-to-a-report

https://docs.microsoft.com/pt-br/dynamics365/customer-engagement/developer/webapi/query-metadata-web-api#querying-entitymetadata-attributes

https://nishantrana.me/2018/10/06/dealing-with-optionset-inside-powerbi-in-dynamics-365-ce/

Comments

*This post is locked for comments

  • tmw Profile Picture tmw
    Posted 18 Mar 2020 at 01:06:47
    Thanks Gustavo! Is there a way to apply this methodology to multiple option sets with a single query? We have many option sets, which would require many queries and new tables. Is there a more elegant way to query D365 and join optionsets with their friendly labels, bringing them into powerbi?
  • tcardoso Profile Picture tcardoso 137
    Posted 07 Jan 2019 at 15:43:39

    great article!