The refresh in Power BI Desktop is fine. But when I try to refresh in Power BI Service, I get the following error: Some data sources may not be listed because of hand-authored queries.
Does anyone have a solution for this? Thank you!
I have the following query:
let
StartValue = 0, // Het startgetal van de lijst
EndValue = Json.Document(Web.Contents("https://app.robaws.com/api/v2/work-orders?page=0&size=100"))[totalPages] - 1, // Het eindgetal van de lijst
StepValue = 1, // De stapgrootte tussen de getallen
Source = List.Generate(
() => StartValue, // De functie om te beginnen met genereren
each _ <= EndValue, // De voorwaarde om te stoppen met genereren
each _ + StepValue // De functie om het volgende getal te genereren
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Page"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Page", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Result", each Json.Document(Web.Contents("https://app.robaws.com/api/v2/work-orders?page=" & [Page] & "&size=100"))),
#"Expanded Result" = Table.ExpandRecordColumn(#"Added Custom", "Result", {"items"}, {"Result.items"}),
#"Expanded Result.items" = Table.ExpandListColumn(#"Expanded Result", "Result.items"),
#"Expanded Result.items1" = Table.ExpandRecordColumn(#"Expanded Result.items", "Result.items", {"id", "logicId", "projectId", "clientId", "date", "clientReference", "status", "remark", "totalExclVat", "totalCostExclVat"}, {"Result.items.id", "Result.items.logicId", "Result.items.projectId", "Result.items.clientId", "Result.items.date", "Result.items.clientReference", "Result.items.status", "Result.items.remark", "Result.items.totalExclVat", "Result.items.totalCostExclVat"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Result.items1",{{"Result.items.id", "ID"}, {"Result.items.logicId", "LogicID"}, {"Result.items.projectId", "ProjectID"}, {"Result.items.clientId", "ClientID"}, {"Result.items.date", "Date"}, {"Result.items.clientReference", "ClientReference"}, {"Result.items.status", "Status"}, {"Result.items.remark", "Remark"}, {"Result.items.totalExclVat", "TotalExclVat"}, {"Result.items.totalCostExclVat", "CostExclVat"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"ID", type text}, {"LogicID", type text}, {"ProjectID", Int64.Type}, {"ClientID", type text}, {"ClientReference", type text}, {"Status", type text}, {"Remark", type text}, {"Date", type date}, {"TotalExclVat", type number}, {"CostExclVat", type number}})
in
#"Changed Type1"
