Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

OData.Feed Include Annotations from CRM

Hi 

 

I'm trying to test connecting Power BI to our UAT Dynamics CRM and pull back a number of columns. The problem i'm having is that some columns have a CRM datat type = Option Set and what I really want is the Label.

 

I think I need to use the IncludeAnnotations option in my call from this link https://docs.microsoft.com/en-nz/powerquery-m/odata-feed but not sure how to actually write the code. An example of trying to just return one column I know I want the lable for is below for the column "Activity" from the Accounts table:

 

 

let
    Source = OData.Feed("https://CompanyA-uat.api.crm5.dynamics.com/api/data/v9.1", null, [#"IncludeAnnotations"="OData.Community.Display.V1.FormattedValue"="*",Implementation="2.0"]),
    accounts_table = Source{[Name="accounts",Signature="table"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(accounts_table,{"activity"})
in
    #"Removed Other Columns"

So i've tried playing around with a few combos of what syntax I could find on other forums, I don't get any errors but none actually return anything other than the origional data:

 

  •  #"IncludeAnnotations"="OData.Community.Display.V1.FormattedValue"="*"
  •  #"IncludeAnnotations"="odata.include-annotations"="*"
  •  IncludeAnnotations="*"

 

Any help on this would be greatley appreciated

6 REPLIES 6
Anonymous
Not applicable

In case this helps someone else: Full info here: https://github.com/rajrao/mypublicnotes/blob/master/PowerBi/OdataRetrieveAnnotations.md

 

let
    Source = OData.Feed("https://myOrg.crm.dynamics.com/api/data/v9.1/opportunities?$select=name,opportunityid,statecode&$filter=fieldXyz ne null", null, [Implementation="2.0",IncludeAnnotations="*"]),
    #"Added Custom" = Table.AddColumn(Source, "StateCodeLabel", each Value.Metadata([statecode])[OData.Community.Display.V1.FormattedValue])
in
    #"Added Custom"

 

v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

"The problem i'm having is that some columns have a CRM datat type = Option Set and what I really want is the Label."

Based on my understanding, original data in your CRM has data with label, but when you import data to Power BI, It doesn't regard the label. 

Could you give an example what your original data in your CRM is and what the data shows in Power BI?

Based on my knowledge, you could use "Query overview in Power BI Desktop" to transform the data.

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi Maggie

 

Sorry for the dealy, I got side tracked with another project at work. In the CRM what I see is the label, so on an Account I might see the Activity as "Operations - Mixed" or "Consultant - General" etc which is what I want. But when I import that column into Power BI what I see are numbers that represent the stored Option Set value. e.g. when I import the column from CRM what it gives me is the stored value of possible option set:

 

100000001
100000002
100000003
100000004
100000005
100000007
100000008
100000009
100000010
100000011
100000013
100000014

 

So on a given entry I will receive in Power BI the number 100000013, but what I want is the label "Consultant - General" that it attached to that number set value.

 

As a quick & dirty way, I could just write some DAX to either replace values or Text.Contains and say if  100000013 then return "Consultant - General" but if the label ever changed I would have to manually update the code, so wanted to avoid this.

 

Thanks

@Anonymous 

Were you ever able to get your labels to dynamically import without DAX to replace the values?  

Anonymous
Not applicable

Hi Tyler-Clark, no I havent found the solution yet, I need to find some time to keep looking for it.

 

What I'm thinking of doing just to get things working is create another table with 2 columns. One with the unique CRM number and another I will have to manually maintain with the label name i want. The other option is add a conditional column and write the mapping in code. But both will require ongoing maintenance if the CRM labels change.

 

Do you have a solution or the same problem? 

@Anonymous 
Sadly, i do not.  I have done quite a bit of testing and using a DAX replace function and just updating it as new fields are adding or old fields are changed seem to be the only viable solution, but with nearly 300 different fields, the function is going to take me quite a bit of time to maintain.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors