Hi,
I have following code I am using to get data from WEB API, I am getting error when I tried to schedule refresh in Powerbi Service.
I am not sure what needs to be changed in code to get it work.
Error:
"You can't schedule refresh for this dataset because the following data sources currently don't support refresh:
- Data source for dtc_noWorkOrder
- Data source for dtc_rejected
Discover Data Sources
Query contains unsupported function. Function name: Web.Contents"
M Query:
let
BaseUrl = "https://forms.logiforms.com/api/1.0/form/371533/data?",
InfoUrl = "https://forms.logiforms.com/api/1.0/form/371533/",
Token = "YXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
EntitiesPerPage = 100,
GetJson = (Url) =>
let Options = [Headers=[Authorization = "Basic " & Token ]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = InfoUrl,
Json = GetJson(Url),
Count = Json[data][form][submissions]
in Count,
GetPage = (Index) =>
let Skip = "page=" & Text.From(Index),
Url = BaseUrl & Skip,
Json = GetJson(Url),
Value = Json[data][records]
in Value,
EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 1 .. PageCount },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
#"Converted to Table" = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"datesubmitted", "Timecard_RecordID", "lfuuid", "User_Name", "User_RecordID", "Rejection_Reason", "User_Functional_Area", "RecordID", "Rejection_Code"}, {"datesubmitted", "Timecard_RecordID", "lfuuid", "User_Name", "User_RecordID", "Rejection_Reason", "User_Functional_Area", "RecordID", "Rejection_Code"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"datesubmitted", type datetime}, {"Timecard_RecordID", Int64.Type}, {"lfuuid", type text}, {"User_Name", type text}, {"User_RecordID", Int64.Type}, {"Rejection_Reason", type text}, {"Rejection_Code", type text}, {"RecordID", Int64.Type}, {"User_Functional_Area", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"datesubmitted", "lfuuid", "RecordID", "Timecard_RecordID", "User_Name", "User_RecordID", "User_Functional_Area", "Rejection_Reason", "Rejection_Code"}),
#"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Rejection_Description", each (
if [Rejection_Code] = "Z001" then
"Wrong Work Order"
else if [Rejection_Code] = "Z002" then
"Wrong Segment"
else if [Rejection_Code] = "Z003" then
"Time Not Approved"
else if [Rejection_Code] = "Z004" then
"Incorrect Day / Time"
else
""
))
in
#"Added Conditional Column"
