Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have seen multiple posts on how to achieve it, but I have trouble adapting my custom REST API function.
Please see below my source function:
(queryID) => let
resultCount = 1000,
otherNameForPage = 0,
URL = "https://baseURL/admin/plugins/explorer/queries/" & Number.ToText(queryID) & "/run",
GetPage = (otherNameForPage) =>
let
content1 = "params={""page"":""" & Number.ToText(otherNameForPage) & """}",
RawData = Json.Document(Web.Contents(
URL,
[
Headers = [
#"api-username"="myUserName",
#"api-key"="myAPIKey",
#"Content-Type" = "application/x-www-form-urlencoded"],
Content = Text.ToBinary(content1)
]
) ),
resultCount = RawData[result_count]
in
if RawData[result_count] = 0 then null else RawData,
Pages = List.Generate(
() => [i = 0, RawData = GetPage(i)],
each [RawData] <> null,
each [i=[i]+1, RawData = GetPage(i)],
//each Record.ToTable([RawData])),
each Table.Combine(let raw = [RawData] in List.Transform(raw[rows], each Table.FromRows({_}, raw[columns])))),
// Output = Table.Join(each Pages, Pages[rows])
Output = Table.Combine(Pages)
in
Output
With this query, I am able to dynamically fetch my records, page after page, by simply providing the ID of my query on the source website.
I read through these already, but it is not clear to me how the solution should apply to my use case:
http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
https://blog.crossjoin.co.uk/2019/04/25/skip-test-connection-power-bi-refresh-failures/
Some small help to set it correctly wuold be greatly appreciated 🙂
Solved! Go to Solution.
I don't like giving up. See my solution below 🙂
(queryID) => let
resultCount = 1000,
otherNameForPage = 0,
GetPage = (otherNameForPage) =>
let
content1 = "params={""page"":""" & Number.ToText(otherNameForPage) & """}",
RawData = Json.Document(Web.Contents(
"https://baseURL/admin/plugins/explorer/queries",
[RelativePath=Number.ToText(queryID) & "/run",
Query=
[
params="{""page"":""" & Number.ToText(otherNameForPage) & """}"
],
Headers = [
#"api-username"="myUsername",
#"api-key"="myAPIKey",
#"Content-Type" = "application/x-www-form-urlencoded"],
Content = Text.ToBinary(content1)
]
) ),
resultCount = RawData[result_count]
in
if RawData[result_count] = 0 then null else RawData,
Pages = List.Generate(
() => [i = 0, RawData = GetPage(i)],
each [RawData] <> null,
each [i=[i]+1, RawData = GetPage(i)],
//each Record.ToTable([RawData])),
each Table.Combine(let raw = [RawData] in List.Transform(raw[rows], each Table.FromRows({_}, raw[columns])))),
// Output = Table.Join(each Pages, Pages[rows])
Output = Table.Combine(Pages)
in
Output
So interesting code @loginerror ! thank you 🙂
is it possible to add this parameter to it "limit=ALL" ?
I tried many combinations but it doesn't work
thanks again 🙂
Hi, @loginerror
Is the issue in the service or desktop?
If in service,you can consider split the original URL into baseURL and RelativePath.
For more details,please refer to this documen.
Using The RelativePath And Query Options With Web.Contents
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I don't like giving up. See my solution below 🙂
(queryID) => let
resultCount = 1000,
otherNameForPage = 0,
GetPage = (otherNameForPage) =>
let
content1 = "params={""page"":""" & Number.ToText(otherNameForPage) & """}",
RawData = Json.Document(Web.Contents(
"https://baseURL/admin/plugins/explorer/queries",
[RelativePath=Number.ToText(queryID) & "/run",
Query=
[
params="{""page"":""" & Number.ToText(otherNameForPage) & """}"
],
Headers = [
#"api-username"="myUsername",
#"api-key"="myAPIKey",
#"Content-Type" = "application/x-www-form-urlencoded"],
Content = Text.ToBinary(content1)
]
) ),
resultCount = RawData[result_count]
in
if RawData[result_count] = 0 then null else RawData,
Pages = List.Generate(
() => [i = 0, RawData = GetPage(i)],
each [RawData] <> null,
each [i=[i]+1, RawData = GetPage(i)],
//each Record.ToTable([RawData])),
each Table.Combine(let raw = [RawData] in List.Transform(raw[rows], each Table.FromRows({_}, raw[columns])))),
// Output = Table.Join(each Pages, Pages[rows])
Output = Table.Combine(Pages)
in
Output
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.