Forum Discussion
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
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"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.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?
