Dear Lydia,
Thanks a lot for sending me this useful link.
I've used it and adapted it with a colleague and I'm now able to import all row items from the REST API.
The issue I'm now confronted with is that eventhough the report displays right through the Power BI Desktop and also the Power BI Service, I get an error message in the Power BI Service dataset section saying: "You can't schedule refresh for this dataset because one or more sources currently don't support refresh." Which is an issue as this report needs indeed to be refreshed on a regular base.
Are you aware whether some instructions from this script could create an issue for refreshing data?
Also, would you advise me to create a new support ticket or do you think that the community members can help
for such script troubleshooting?
Original script:
let
Source = Json.Document(Web.Contents("https://mysite.com/api/v1/scopes?subproject_id=12")),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "scope_status_id", "request_id"}, {"id", "scope_status_id", "request_id"})
in
#"Expanded Column1"
Updated script:
let
Source = Json.Document(Web.Contents("https://mysite.com/api/v1/scopes?subproject_id=12")),
BaseUrl = "https://mysite.com/api/v1/scopes?subproject_id=12",
EntitiesPerPage = 100,
GetJson = (Url) =>
let RawData = Web.Contents(Url),
Json = Json.Document(RawData)
in Json,
GetTotalEntities = () =>
let Json = GetJson(BaseUrl),
Total = Json[total]
in Total,
GetPage = (Index) =>
let Skip = "skip=" & Text.From(Index * EntitiesPerPage),
Url = BaseUrl & "&" & Skip,
Json = GetJson(Url),
Value = Json[data]
in Value,
GetUrl = (Index) =>
let Skip = "skip=" & Text.From(Index * EntitiesPerPage),
Url = BaseUrl & "&" & Skip
in Url,
EntityCount = List.Max({ EntitiesPerPage, GetTotalEntities() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
URLs = List.Transform(PageIndices, each GetUrl(_)),
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", {"id", "scope_status_id", "request_id}, {"id", "scope_status_id", "request_id"})
in
#"Expanded Column1"
