Query Dynamics CRM PartyList

[Gerald23]

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], ">", "&gt;"), "<", "&lt;"), """", "&quot;"), 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"

[Ya_Katrin]

Hi!

Did you found the solution?

[v-jingzhang]

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

[Gerald23]

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