Forum Discussion
CRM and missing fields
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 .
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.
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"
