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