Query Dynamics CRM PartyList

Gerald23 — Fri, 23 Apr 2021 13:02:16 GMT

Hi Everyone,

Anyone has experience with getting data from Dynamics CRM from PartyList type fields? I try to get phone call information into power bi but i can't get the data from the To and From field because these are PartyList fields.

This is the query i use and it works but it only returns Null values for these fields.

let GetResults = (z as text, x as number) => let S = Json.Document(Web.Contents(ServiceRootURL, [RelativePath="/phonecalls", Headers=[Prefer="odata.include-annotations=*"],Query=[fetchXml=" <fetch distinct=""True"" page=""" & Text.From(x) & """ paging-cookie=""" & z & """> <entity name=""phonecall""> <all-attributes /> <link-entity name=""activityparty"" from=""activityid"" to=""activityid"" link-type=""inner"" alias=""ad""><filter type=""and""><condition attribute=""participationtypemask"" operator=""in""><value>1</value><value>2</value></condition></filter></link-entity> </entity> </fetch>"]])), P = try Xml.Document(S[#"@Microsoft.Dynamics.CRM.fetchxmlpagingcookie"]) otherwise null, R = if P <> null then List.Combine({S[value],@GetResults(Text.Replace(Text.Replace(Text.Replace(Uri.Parts("http://a.b?d=" & Uri.Parts("http://a.b?d=" & P{0}[Attributes]{1}[Value])[Query][d])[Query][d], ">", ">"), "<", "<"), """", """), x + 1)}) else S[value] in R, ResultsList = GetResults("", 1), ResultsTable = if List.IsEmpty(ResultsList) then #table( type table[ #"Subject"= text, #"Activity Status (statecode)"= text, #"Activity Status"= text, #"Priority (prioritycode)"= text, #"Priority"= text, #"Due"= text, #"Created By (createdby)"= text, #"Created By"= text, #"Regarding (regardingobjectid)"= text, #"Regarding"= text, #"activityid"= text, #"Call To (to)"= text, #"Call To"= text, #"Call From (from)"= text, #"Call From"= text ],{}) else #"Converted to Table", #"Converted to Table" = Table.FromList(ResultsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", { "subject", "statecode", "statecode@OData.Community.Display.V1.FormattedValue", "prioritycode", "prioritycode@OData.Community.Display.V1.FormattedValue", "scheduledend@OData.Community.Display.V1.FormattedValue", "_createdby_value", "_createdby_value@OData.Community.Display.V1.FormattedValue", "_regardingobjectid_value", "_regardingobjectid_value@OData.Community.Display.V1.FormattedValue", "activityid", "_to_value", "_to_value@OData.Community.Display.V1.FormattedValue", "_from_value", "_from_value@OData.Community.Display.V1.FormattedValue" }, { "subject", "statecode", "statecode@OData.Community.Display.V1.FormattedValue", "prioritycode", "prioritycode@OData.Community.Display.V1.FormattedValue", "scheduledend@OData.Community.Display.V1.FormattedValue", "_createdby_value", "_createdby_value@OData.Community.Display.V1.FormattedValue", "_regardingobjectid_value", "_regardingobjectid_value@OData.Community.Display.V1.FormattedValue", "activityid", "_to_value", "_to_value@OData.Community.Display.V1.FormattedValue", "_from_value", "_from_value@OData.Community.Display.V1.FormattedValue" }), #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1", { {"subject", "Subject"}, {"statecode", "Activity Status (statecode)"}, {"statecode@OData.Community.Display.V1.FormattedValue", "Activity Status"}, {"prioritycode", "Priority (prioritycode)"}, {"prioritycode@OData.Community.Display.V1.FormattedValue", "Priority"}, {"scheduledend@OData.Community.Display.V1.FormattedValue", "Due"}, {"_createdby_value", "Created By (createdby)"}, {"_createdby_value@OData.Community.Display.V1.FormattedValue", "Created By"}, {"_regardingobjectid_value", "Regarding (regardingobjectid)"}, {"_regardingobjectid_value@OData.Community.Display.V1.FormattedValue", "Regarding"}, {"activityid", "activityid"}, {"_to_value", "Call To (to)"}, {"_to_value@OData.Community.Display.V1.FormattedValue", "Call To"}, {"_from_value", "Call From (from)"}, {"_from_value@OData.Community.Display.V1.FormattedValue", "Call From"} }), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", { {"Subject", type text}, {"Activity Status (statecode)", type text}, {"Activity Status", type text}, {"Priority (prioritycode)", type text}, {"Priority", type text}, {"Due", type datetime}, {"Created By (createdby)", type text}, {"Created By", type text}, {"Regarding (regardingobjectid)", type text}, {"Regarding", type text}, {"activityid", type text}, {"Call To (to)", type text}, {"Call To", type text}, {"Call From (from)", type text}, {"Call From", type text} }) , #"Result" = if List.IsEmpty(ResultsList) then ResultsTable else #"Changed Type" in #"Result"

Re: Query Dynamics CRM PartyList

v-jingzhang — Tue, 27 Apr 2021 10:26:44 GMT

Hi Gerald23

I doubt whether Power BI supports the PartyList type from the Dynamics CRM. I will try to find if there is any workaround to this.

Regards,
Community Support Team _ Jing

Re: Query Dynamics CRM PartyList

Gerald23 — Thu, 29 Apr 2021 07:13:02 GMT

Ok i will also continue searching and in case i find something i will post it here for everyone that has the same problem in the future

Re: Query Dynamics CRM PartyList

Ya_Katrin — Tue, 26 Sep 2023 14:59:33 GMT

Hi!

Did you found the solution?

topic Re: Query Dynamics CRM PartyList in Power Query

Query Dynamics CRM PartyList

Re: Query Dynamics CRM PartyList

Re: Query Dynamics CRM PartyList

Re: Query Dynamics CRM PartyList