Hello Pals,
I am struggling to run the M code in Power BI service to refresh the data while it works fine in desktop. After i publish to service, the dataset renders an error: “Query contains unsupported function. Function name: Web.Contents”.
Can you please assist fix this issue by providing the right code to use ? We are using below reference api documentaion from the vendor provider.
https://docs.learninglocker.net/http-xapi-statements/#get-statements
Original M Script: (working in desktop but fails in Service)
let
url = “https://MainUrlHere.com”,
part = “/data/xAPI/statements”,
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url, [Headers=[Authorization=”Basic token comes here”, #”X-Experience-API-Version”=”1.0.3″]])),
data = try Source[statements] otherwise null,
next = try Source[more] otherwise null,
Statements = [Data=data, Next=next]
in
Statements,
GeneratedList =
List.Generate(
()=>[i=0, Statements = FnGetOnePage(url & part)],
each [Statements][Data]null,
each [i=[i]+1, Statements = FnGetOnePage(url & [Statements][Next])],
each [Statements][Data]),
#”Converted to Table” = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandListColumn(#”Converted to Table”, “Column1″),
#”Expanded Column2″ = Table.ExpandRecordColumn(#”Expanded Column1”, “Column1”, {“id”, “actor”, “verb”, “context”, “timestamp”, “object”, “stored”, “authority”, “version”}, {“id”, “actor”, “verb”, “context”, “timestamp”, “object”, “stored”, “authority”, “version”})
in
#”Expanded Column2″
The API sends the data paginated, 100 elements (statements) by page. You can get all the data calling the API with the value of
“more” in each call.
PostMan output returned by the API:
“more”: “/data/xAPI/statements/?cursor=5ec825174a6905632eec1a8f”,
“statements”: [
{
“id”: “ddb382ab-fff6-4fac-9ffe-ea000722ac5e”,
“actor”: {
“objectType”: “Agent”,
“name”: “123456789”,
“mbox”: “mailto:abc@uat.com”
},
“verb”: {
“id”: “http://id.tincanapi.com/verb/viewed”,
“display”: {
“en-US”: “viewed”
}
},
“stored”: “2020-05-25T05:09:42.745Z”,
“authority”: {
“objectType”: “Agent”,
“name”: “beauat”,
“mbox”: “mailto:abc@uat.com”
},…….
“statements” is the list of elements to transform to Table in PowerBI. “more” is the relative path to add to the endpoint to call for the next page.
Modified M Script:
Per suggestion from other forums, i have added relative path but the script keeps running indefinite times in Desktop and does not stop. May be i am missing unknown. For testing purpose, i passed iterations as 5 and used in first each statement in List.Generate, it stop execution but same set of data is repeated, however i can publish to service and refresh without any errors.
let
url = "https://lrs-qa.brandexcellenceacademy.com",
part = "/data/xAPI/statements",
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents("https://lrs-qa.brandexcellenceacademy.com",
[
RelativePath="/data/xAPI/statements",
Headers=[Authorization="Basic ZWMyNWNmMWZjMTcxODE4MDE0OTcxOTJlNmNjMmM4YzBmYzM1MzJhNDpiNGU1ZjkwYWU1YmI0M2FkMDlmNjBhYjg1ZjIxMTdkZDVjNmE0Zjc5", #"X-Experience-API-Version"="1.0.3"]
]
)),
data = try Source[statements] otherwise null,
next = try Source[more] otherwise null,
Statements = [Data=data, Next=next]
in
Statements,
GeneratedList =
List.Generate(
()=>[i=0, Statements = FnGetOnePage(url & part)],
each [Statements][Data]<>null,
each [i=[i]+1, Statements = FnGetOnePage(url & [Statements][Next])],
each [Statements][Data]),
#"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "actor", "verb", "context", "timestamp", "object", "stored", "authority", "version"}, {"id", "actor", "verb", "context", "timestamp", "object", "stored", "authority", "version"})
in
#"Expanded Column2"
I would really appreciate any assistance from this community.
Thanks in advance,
Apoorva.
