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
Select Contact as entity and localize the optionset field gendercode from Attributes section (tip: use Text Search at the top).
From the right pane side, navigate to OptionSet option and click on the Get Options button.
(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.
Create a new blank query.
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”}.
As suggestion, rename the recent query for a more meaningful name.
(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.
Select the new table (contacts-gendercodelabel).
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).
Now, the column has been added to original table (contact) with the proper value:label entries.
Select the new column added to the query and check the label.
As suggestion, rename the recent column for a more meaningful name.
At end, we have a column with the proper labels to use into the visualizations.
Referencing articles
https://nishantrana.me/2018/10/06/dealing-with-optionset-inside-powerbi-in-dynamics-365-ce/
*This post is locked for comments