Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm new in this M world.
So the problem is: I wanna increment the parameter page (++1), until the last page. In the future get the data on the page and go to the next page.
EX:
https://api.github.com/repos/" & login & "/" & repository & "/issues?state=all&sort=created&page=" & Text.From(page) // page is my variable
Thinking as JAVA man we just adding a variable and use for/while depending the situation.
Now, how to do that using M language?
I did the homework and this sites have some information:
https://seddryck.wordpress.com/2017/01/07/do-while-loop-with-power-bi-m-language/
But it's not working. 😞
Anyone can help me?
Thanks.
@Vinicius,
What error message do you get? Please check if the following blogs help in your scenario.
Iterating over multiple pages of web data using Power Query
Power BI Using List.Generate to Reduce API Calls with Pagination
Regards,
Lydia
Thanks for help.
I used this method:
1 - Get Data>Web .... Conect API (to simplify the conection with header, parametres etc)
2 - Advanced Editor on this data and edit like this (Name = GetData):
(page as number) as table =>
let
Fonte = Json.Document(Web.Contents("https://api.powerbihelp.com.br/orders?page=" & Number.ToText(page) & "&per_page=100", [Headers=[Accept="application/json", ContentType="application/json", #"x-accountmanager-key"="", #"x-api-key"="", #"x-user-email"=""]])),
Data1 = Fonte{1}[Data],
RemoveBottom = Table.RemoveLastN(Data1,3)
in
Fonte
And creat a query blank:
List.Generate( ()=>
[Result= try GetData(1) otherwise null, Page = 1],
each [Result] <> null,
each [Result = try GetData([Page]+1) otherwise null, Page =[Page]+1],
each [Result])
In theory, my opnion, its correct. But nothing appears juts "List" in blank.
What I'm doing wrong?
Thanks.
@Vinicius,
Do you get correct result entering a page value in the following function?
(page as number) as table => let Fonte = Json.Document(Web.Contents("https://api.powerbihelp.com.br/orders?page=" & Number.ToText(page) & "&per_page=100", [Headers=[Accept="application/json", ContentType="application/json", #"x-accountmanager-key"="", #"x-api-key"="", #"x-user-email"=""]])), Data1 = Fonte{1}[Data], RemoveBottom = Table.RemoveLastN(Data1,3) in Fonte
If you get correct result, add a list containing all pages, and add a custom column following the guide in this blog.
Regards,
Lydia
"If you get correct result, add a list containing all pages" you mean a table?
I'm using an API that limit the result, so I need to change the page.
I'm searching and testing ways to do it, but I don´t have enough knowledge.
I read the information on that link (/iterating-over-multiple-pages-of-web-data-using-power-query/) but the example used web page and I'm using API Json.
If I put Web.Page = StatusCode=-2146697208 DataSource.Error:
I'm trying this; https://datachant.com/2016/06/27/cursor-based-pagination-power-query/
Thanks again.
@Vinicius,
In your scenario, firstly, add a new blank query in Power BI Desktop, then paste the following code in Advanced Editor of the blank query and check if you get a similar screenshot as follows.
(page as number) as table => let Fonte = Json.Document(Web.Contents("https://api.powerbihelp.com.br/orders?page=" & Number.ToText(page) & "&per_page=100", [Headers=[Accept="application/json", ContentType="application/json", #"x-accountmanager-key"="", #"x-api-key"="", #"x-user-email"=""]])), Data1 = Fonte{1}[Data], RemoveBottom = Table.RemoveLastN(Data1,3) in RemoveBottom
Secondly, if you get a screenshot about function, just enter a page value(e.g.3) to invoke the function and check if it returns correct result.
Thirdly, if the above steps works, add a blank query in Power BI Desktop and create a list that contains all the values of your pages, then add custom column as described in this blog: https://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/ .
Regards,
Lydia Zhang
I'm trying the first step and Power BI is showing:
Error in query ''. Expression.Error: We were unable to convert the value "https: //api.Teste.c ..." into type List.
Details:
Value = https: //api.teste.com.br/orders?page = 3
Type = Type
I'm using:
(page as number) as table =>
let
AuthKey = "",
Fonte = "https://api.test.com.br/orders?page=3", //I removed the parameter to change the page to test it
body = "{}",
Source = Json.Document(Web.Contents(Fonte,[
Headers = [#"Authorization"="",
#"x-api-key"="",
#"Content-Type"="application/json",
#"x-accountmanager-key"="",
#"x-user-email"="",
#"Accept"=""]
]
)),
Data1 = Fonte{1}[Data],
RemoveBottom = Table.RemoveLastN(Data1,3)
in
RemoveBottom
@Vinicius,
Please use the Web connector in Power BI Desktop to connect to a single page of your web, then post the code generated in Advanced Editor and post a screenshot of the data status in Query Editor.
Regards,
Lydia