CRM and missing fields

[dglsmason]

With Power BI Desktop, several fields are missing when I connect via Dynamics CRM Online. For example, OpportunitySet is missing Status and Status Reason. Why? Also, when I use the Power BI web (v 11.0.9167.510), Status and Status Reason appear but other fields are missing. Why?  Thanks

[Surendra_thota]

Hi All

we have created power bi reports on Dynamic CRM using power bi desktop and we are using ownerid.name column from incidentset entity , we are getting value in desktop but when we publish to powerbi services we are missing owerid.name value after refresh . what is the issue , how to solve this .

[Anonymous]

I am wondering if you were able to get this resolved as I am having the same issue. I am connected to our on-premise CRM and see all of the data in desktop but several fields disappear in Power BI services. Any help would be greatly appreciated.

[Syndicate_Admin]

Yes you can use this code to get these

let

DataList = List.Generate(
() => [
SourceURI="https:// "URL" /api/data/v9.1/stringmaps"
,Pagecount=0
,Stringmaps = {}
,Source = []
,ErrorTest = try Source = []
]
,each if [ErrorTest][HasError] then false else true

,each [
ErrorTest = try Source = Json.Document(Web.Contents([SourceURI]))
,Source = Json.Document(Web.Contents([SourceURI]))
,SourceURI = Record.Field(Source,"@odata.nextLink")
,Stringmaps = Source[value]
,Pagecount = [Pagecount] + 1
]
),
#"Converted to Table" = Table.FromList(DataList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Stringmaps"}, {"Column1.Stringmaps"}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Expanded Column1", {"Column1.Stringmaps"}),
#"Expanded Column1.Stringmaps" = Table.ExpandListColumn(#"Removed Errors", "Column1.Stringmaps"),
#"Removed Blank Rows" = Table.SelectRows(#"Expanded Column1.Stringmaps", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Expanded Column1.Stringmaps1" = Table.ExpandRecordColumn(#"Removed Blank Rows", "Column1.Stringmaps", {"value", "attributename", "objecttypecode", "attributevalue"}, {"value", "attributename", "objecttypecode", "attributevalue"}),
#"Sorted Rows" = Table.Sort(#"Expanded Column1.Stringmaps1",{{"objecttypecode", Order.Ascending},{"attributename", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"attributename", "objecttypecode"}, {{"Count", each _, type table [value=text, attributename=text, objecttypecode=text, attributevalue=number]}}),
#"Grouped Rows1" = Table.Group(#"Grouped Rows", {"objecttypecode"}, {{"Count", each _, type table [attributename=text, objecttypecode=text, Count=table]}}),
account = #"Grouped Rows1"{[objecttypecode="account"]}[Count],
accountcategorycode_account = account{[attributename="businesstypecode",objecttypecode="account"]}[Count],
#"Dubbele waarden verwijderd" = Table.Distinct(accountcategorycode_account, {"attributevalue"}),
#"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Dubbele waarden verwijderd",{{"value", "Option"}, {"attributevalue", "Value"}}),
#"Andere kolommen verwijderd" = Table.SelectColumns(#"Namen van kolommen gewijzigd",{"Option", "Value"})
in
#"Andere kolommen verwijderd"

[jpsolares]

EstimatedValues is missing, but with edit query u can add since money field is not plain, u have to add value field for the table.

greetings,

[dglsmason]

Correctiona/Update:

With Power BI Desktop client - all of the entities and fields are available. For fields such as Status and Status Reason, I had to go to Edit Query in order to see they were included (can then Expand the column to include portions needed, such as ID and Name). The Power BI Web tool uses the Content Packm which does not support custom fields.

[StNick]

Unfortunately I've been using the Desktop client and I still have missing fields.

[TPalmer]

The Desktop client will have access to the full CRM Online OData feed so if the fields are available you should be able to access them. The feed will also expose all the data you have access to in CRM so it's suggested that you apply filters (such as created in the last year) to ensure you're dealing with a managable set for your reporting.

While the content pack also uses the CRM Online OData feed, it has customized queries that may join values from multiple tables. As another user said, it's possible you need to do a transformation such as expand, in order to expose those fields as you're seeing them in the content pack. The documenation on the CRM Online OData feed should provide more guidance on where to find each field.

[alex13p]

I'm still having this problem, 

Was a cause / solution found to fix it?

I need to create a report for my organization that shows leads by their status and status reasons but these aren't showing in my data feed on Power BI. 

I've also seen videos online where people get data from their CRM instances and it comes down with just the entity names such as 'Lead', 'Account', 'Contact', but for me I see various options for data'sets', such as 'AccountLeadsSet', 'AccountSet'... or 'ContactLeadsSet', 'ContactOrdersSet', 'ContactQuotesSet', 'ContactSet'.... I don't know if this is related and perhaps i'm not connecting to my CRM instance in the right way - happy to create a seperate thread about this if it's not related. 

I'm connecting through https://myorganization.crmXX.dynamics.com/xrmservices/2011/organizationdata.svc

Trying to search online for answers around this isn't bringing back much - hoping someone on here can help.

Thanks

[Greg_Deckler]

Did you use the Content Pack to connect to CRM Online? I used the content pack and I see both Status and Status Reason under OpportunitySet.

[dglsmason]

Thanks for your reply! The Content Pack is what I used from web, but from the Power BI Client, selecting "Dynamics CRM Online" for Get Data is the same as selecting oData. Without editing the query, Status and Status Reason are not included. It's like the Content Pack is really only available from Power BI on the web and not the client.

[StNick]

I'm still in the midist of initial tests and I had a similar issue with missing fields. In the past when I've selected multiple (2-3) entities to include in a dataset, it's taken 5+ hours of downloading before some issue or another comes up and I have to cancel or it's stopped. Today I selected 9 entities as a test and it finished within an hour. However those entities were missing fields.

it seems to me that some issue occured while it was downloading data and it falsely reported completion. I ran another test, removing my multple entity selection and only chose one entity, and after an hour it finished and had all fields.