Hi all,
I trust you are well
Can you please help?
I am trying to connect power bi to a dynamics 365 view using xrmtoolbox
i have extracted the code but i am getting error message
"DataSource.Error: Web.Contents failed to get contents"
this is my code:
let
GetResults = (z as text, x as number) =>
let
S = Json.Document(Web.Contents(ServiceRootURL, [RelativePath="/queueitems", Headers=[Prefer="odata.include-annotations=*"],Query=[fetchXml="
<fetch distinct=""False"" page=""" & Text.From(x) & """ paging-cookie=""" & z & """>
<entity name=""queueitem""> <all-attributes />
<filter type=""and""><condition attribute=""queueid"" operator=""eq"" uiname=""Networks with New Documents"" uitype=""queue"" value=""{5A5D9A7E-E115-EA11-A811-000D3A7ED588}"" /></filter>
<link-entity name=""inf_network"" from=""inf_networkid"" to=""objectid"" visible=""false"" link-type=""outer"" alias=""a_87fe9e109f0bea11a811000d3a86d6ba""><attribute name=""inf_customer"" /><attribute name=""inf_type"" /></link-entity>
<link-entity alias=""a_4317a47620834b68a2f9e7ca23b46f1d"" name=""task"" from=""activityid"" to=""objectid"" link-type=""outer"" visible=""false""><attribute name=""regardingobjectid"" /></link-entity>
<link-entity name=""inf_customerjourneyactivity"" from=""activityid"" to=""objectid"" visible=""false"" link-type=""outer"" alias=""a_2cc845899cf9e911a814000d3a86d716""><attribute name=""regardingobjectid"" /><attribute name=""isc_fueltype"" /><attribute name=""isc_numberoffiles"" /></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[ #"queueitemid"= text,
#"Entered Queue"= text,
#"Worked By (workerid)"= text,
#"Worked By"= text,
#"Type (objecttypecode)"= text,
#"Type"= text,
#"Object (objectid)"= text,
#"Object"= text,
#"Customer (inf_customer) (Object)"= text,
#"a_87fe9e109f0bea11a811000d3a86d6ba.inf_customer(Formatted Value) (Object)"= text,
#"Type (Object) (Object)"= text,
#"a_87fe9e109f0bea11a811000d3a86d6ba.inf_type(Formatted Value) (Object)"= text,
#"Regarding (regardingobjectid) (Object)"= text,
#"a_4317a47620834b68a2f9e7ca23b46f1d.regardingobjectid(Formatted Value) (Object)"= text,
#"Network (regardingobjectid) (Object)"= text,
#"a_2cc845899cf9e911a814000d3a86d716.regardingobjectid(Formatted Value) (Object)"= text,
#"Fuel Type (Object) (Object)"= text,
#"a_2cc845899cf9e911a814000d3a86d716.isc_fueltype(Formatted Value) (Object)"= text,
#"Number Of Files (Object)"= number ],{})
else #"Converted to Table",
#"Converted to Table" = Table.FromList(ResultsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1",
{
"queueitemid",
"enteredon@OData.Community.Display.V1.FormattedValue",
"_workerid_value",
"_workerid_value@OData.Community.Display.V1.FormattedValue",
"objecttypecode",
"objecttypecode@OData.Community.Display.V1.FormattedValue",
"_objectid_value",
"_objectid_value@OData.Community.Display.V1.FormattedValue",
"a_87fe9e109f0bea11a811000d3a86d6ba.inf_customer",
"a_87fe9e109f0bea11a811000d3a86d6ba.inf_customer@OData.Community.Display.V1.FormattedValue",
"a_87fe9e109f0bea11a811000d3a86d6ba.inf_type",
"a_87fe9e109f0bea11a811000d3a86d6ba.inf_type@OData.Community.Display.V1.FormattedValue",
"a_4317a47620834b68a2f9e7ca23b46f1d.regardingobjectid",
"a_4317a47620834b68a2f9e7ca23b46f1d.regardingobjectid@OData.Community.Display.V1.FormattedValue",
"a_2cc845899cf9e911a814000d3a86d716.regardingobjectid",
"a_2cc845899cf9e911a814000d3a86d716.regardingobjectid@OData.Community.Display.V1.FormattedValue",
"a_2cc845899cf9e911a814000d3a86d716.isc_fueltype",
"a_2cc845899cf9e911a814000d3a86d716.isc_fueltype@OData.Community.Display.V1.FormattedValue",
"a_2cc845899cf9e911a814000d3a86d716.isc_numberoffiles"
},
{
"queueitemid",
"enteredon@OData.Community.Display.V1.FormattedValue",
"_workerid_value",
"_workerid_value@OData.Community.Display.V1.FormattedValue",
"objecttypecode",
"objecttypecode@OData.Community.Display.V1.FormattedValue",
"_objectid_value",
"_objectid_value@OData.Community.Display.V1.FormattedValue",
"a_87fe9e109f0bea11a811000d3a86d6ba.inf_customer",
"a_87fe9e109f0bea11a811000d3a86d6ba.inf_customer@OData.Community.Display.V1.FormattedValue",
"a_87fe9e109f0bea11a811000d3a86d6ba.inf_type",
"a_87fe9e109f0bea11a811000d3a86d6ba.inf_type@OData.Community.Display.V1.FormattedValue",
"a_4317a47620834b68a2f9e7ca23b46f1d.regardingobjectid",
"a_4317a47620834b68a2f9e7ca23b46f1d.regardingobjectid@OData.Community.Display.V1.FormattedValue",
"a_2cc845899cf9e911a814000d3a86d716.regardingobjectid",
"a_2cc845899cf9e911a814000d3a86d716.regardingobjectid@OData.Community.Display.V1.FormattedValue",
"a_2cc845899cf9e911a814000d3a86d716.isc_fueltype",
"a_2cc845899cf9e911a814000d3a86d716.isc_fueltype@OData.Community.Display.V1.FormattedValue",
"a_2cc845899cf9e911a814000d3a86d716.isc_numberoffiles"
}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",
{
{"queueitemid", "queueitemid"},
{"enteredon@OData.Community.Display.V1.FormattedValue", "Entered Queue"},
{"_workerid_value", "Worked By (workerid)"},
{"_workerid_value@OData.Community.Display.V1.FormattedValue", "Worked By"},
{"objecttypecode", "Type (objecttypecode)"},
{"objecttypecode@OData.Community.Display.V1.FormattedValue", "Type"},
{"_objectid_value", "Object (objectid)"},
{"_objectid_value@OData.Community.Display.V1.FormattedValue", "Object"},
{"a_87fe9e109f0bea11a811000d3a86d6ba.inf_customer", "Customer (inf_customer) (Object)"},
{"a_87fe9e109f0bea11a811000d3a86d6ba.inf_customer@OData.Community.Display.V1.FormattedValue", "a_87fe9e109f0bea11a811000d3a86d6ba.inf_customer(Formatted Value) (Object)"},
{"a_87fe9e109f0bea11a811000d3a86d6ba.inf_type", "Type (Object) (Object)"},
{"a_87fe9e109f0bea11a811000d3a86d6ba.inf_type@OData.Community.Display.V1.FormattedValue", "a_87fe9e109f0bea11a811000d3a86d6ba.inf_type(Formatted Value) (Object)"},
{"a_4317a47620834b68a2f9e7ca23b46f1d.regardingobjectid", "Regarding (regardingobjectid) (Object)"},
{"a_4317a47620834b68a2f9e7ca23b46f1d.regardingobjectid@OData.Community.Display.V1.FormattedValue", "a_4317a47620834b68a2f9e7ca23b46f1d.regardingobjectid(Formatted Value) (Object)"},
{"a_2cc845899cf9e911a814000d3a86d716.regardingobjectid", "Network (regardingobjectid) (Object)"},
{"a_2cc845899cf9e911a814000d3a86d716.regardingobjectid@OData.Community.Display.V1.FormattedValue", "a_2cc845899cf9e911a814000d3a86d716.regardingobjectid(Formatted Value) (Object)"},
{"a_2cc845899cf9e911a814000d3a86d716.isc_fueltype", "Fuel Type (Object) (Object)"},
{"a_2cc845899cf9e911a814000d3a86d716.isc_fueltype@OData.Community.Display.V1.FormattedValue", "a_2cc845899cf9e911a814000d3a86d716.isc_fueltype(Formatted Value) (Object)"},
{"a_2cc845899cf9e911a814000d3a86d716.isc_numberoffiles", "Number Of Files (Object)"}
}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",
{
{"queueitemid", type text},
{"Entered Queue", type datetime},
{"Worked By (workerid)", type text},
{"Worked By", type text},
{"Type (objecttypecode)", type text},
{"Type", type text},
{"Object (objectid)", type text},
{"Object", type text},
{"Customer (inf_customer) (Object)", type text},
{"a_87fe9e109f0bea11a811000d3a86d6ba.inf_customer(Formatted Value) (Object)", type text},
{"Type (Object) (Object)", type text},
{"a_87fe9e109f0bea11a811000d3a86d6ba.inf_type(Formatted Value) (Object)", type text},
{"Regarding (regardingobjectid) (Object)", type text},
{"a_4317a47620834b68a2f9e7ca23b46f1d.regardingobjectid(Formatted Value) (Object)", type text},
{"Network (regardingobjectid) (Object)", type text},
{"a_2cc845899cf9e911a814000d3a86d716.regardingobjectid(Formatted Value) (Object)", type text},
{"Fuel Type (Object) (Object)", type text},
{"a_2cc845899cf9e911a814000d3a86d716.isc_fueltype(Formatted Value) (Object)", type text},
{"Number Of Files (Object)", type number}
})
,
#"Added Link" = Table.AddColumn(#"Changed Type", "Link", each Dyn365CEBaseURL & "/main.aspx?etn=queueitem&pagetype=entityrecord&id=%7b"& [queueitemid]&"%7d"),
#"Result" = if List.IsEmpty(ResultsList)
then ResultsTable
else #"Added Link"
in
#"Result"
