Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I hope you can help me.
I am trying to use List.Generate to retrieve the number of pages my organisational page has. From each page I want to extract the records from the field [Results].
I start retrieving page 0 https://photos.app.goo.gl/c6kfDYViNomNuoVT7. I know Page 11 is the last one because Page 11 doesn't have the field [Next] https://photos.app.goo.gl/RDfEpCHMQNKc1GBU6. So, for now I have a query for each of the 11 pages and combine all results in one single table. I guess the script has to add 1 until [Next] doesn´t exist.
I got the following script following the BI Account blog https://www.thebiccountant.com/2020/05/15/miss-last-page-paging-power-bi-power-query/, so maybe @ImkeF can help me here.
List.Generate ( ()=>
[Result = Json.Document(Web.Contents("https://x/api/4.2/selections/3"))],
each [Result][Next] <> null,
each [Result = Json.Document(Web.Contents(Text.Combine({"x/api/4.2/selections/3", Text.Replace([Result][Next], "?cursor=", "/")})))]
)
However it gives an error https://photos.app.goo.gl/EXdPk8vVHT3Ndw2Y7
Thanks for the help.
Solved! Go to Solution.
Hi,
I thought I would share the solution. Again, thanks for your help.
let
GetData =
(PagePath as number) =>
let
Source =
Json.Document(Web.Contents("x",
[
RelativePath =
Text.Combine(
{
"/",
Text.From(PagePath)
}
)
]
))
in
Source,
Source =
List.Generate(
() =>
[
page = 0,
Page = GetData(page)
],
each not List.IsEmpty([Page][Results]),
each
[
page = [page] + 1,
Page = GetData(page)
]
),
Hi,
I thought I would share the solution. Again, thanks for your help.
let
GetData =
(PagePath as number) =>
let
Source =
Json.Document(Web.Contents("x",
[
RelativePath =
Text.Combine(
{
"/",
Text.From(PagePath)
}
)
]
))
in
Source,
Source =
List.Generate(
() =>
[
page = 0,
Page = GetData(page)
],
each not List.IsEmpty([Page][Results]),
each
[
page = [page] + 1,
Page = GetData(page)
]
),
Hi @Anonymous
If you click the word Error it will show you the error message.
If you click beside the word Error it will show you the contents of the error message on the bottom of your screen.
When you try my code and get a list of records, what is in the records? Does my code ever stop? If it does then that indicates it is paging through the API correctly. What is in the last Record? Is there a Next field? Without actually having access to the API it's hard to be certain what is going on.
As with the error, you can click beside the word Record to see the contents of that Record.
You initially stated that you wanted the Record from the results but I think you are actually after the Lists from the results. List and Record have specific meanings in Power Query. So this code is more likely to give you what you want.
let
url = "https://x/",
path = "api/4.2/selections/3",
Source = List.Generate (
()=> [ Result = Json.Document(Web.Contents(url & path)) ],
each [Result][Next] <> null,
each [Result = Json.Document(Web.Contents(url & [Result][Next]))],
each [Result][Results]
)
in
Source
This should give you a list (column) of Lists, which are the contents of the Results field in the returned Web.Contents Result.
Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
Hi Philip,
thanks for getting back to me. Yes, you're right, I want the lists. Sorry if this confused you, I'm not very good with M... I think both scripts are good at accessing the records from page 1, but it looks like they don't move to next pages. See below.
Both of your proposals gave a lot of results, millions. Both were fine in terms of providing the records I wanted (although the second script was better with less steps needed than the first script). However, both proposals give millions of rows. Each row represents a project, and I know I should have less than 1100 projects (100 projects per page, and I have 11 pages on my website).
Also I can tell both scripts are only retrieving results from page 0 https://photos.app.goo.gl/uYd8C6LqcwKHttq7A and don't move to page 1, etc. until page 11 (when there's no Next field https://photos.app.goo.gl/g8ChgZ76F3ECnkTa6 ). In Script 1 I get a step where I can see all the fields from the json file, see the json fiel fields and notice it's always retrieving from page 0. Where in the second script I don't have a stept to see the json fields, but I see that rows from row 101 are repeated (row 101 is the same value as row 1, and so on). So, looks like the script doesn't move to the next page .
Script 1 https://photos.app.goo.gl/SqK1yBSdT9cdo51k6 (see a row for each project. Each page has a List, which has 100 values, each a project).
let
url = "https://x",
path = "api/4.2/selections/3",
Source = List.Generate (
()=> [ Result = Json.Document(Web.Contents(url & path)) ],
each [Result][Next] <> null,
each [Result = Json.Document(Web.Contents(url & [Result][Next]))]
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Result"}, {"Result"}),
#"Expanded Result" = Table.ExpandRecordColumn(#"Expanded Column1", "Result", {"Results"}, {"Results"}),
#"Expanded Results" = Table.ExpandListColumn(#"Expanded Result", "Results"),
#"Expanded Results1" = Table.ExpandRecordColumn(#"Expanded Results", "Results", {"_self"}, {"_self"}),
#"Expanded _self" = Table.ExpandRecordColumn(#"Expanded Results1", "_self", {"APIURL"}, {"APIURL"})
in
#"Expanded _self"
Script 2 https://photos.app.goo.gl/ufcbCyx1viPUDDDw8
let
url = "https://x/",
path = "api/4.2/selections/3",
Source = List.Generate (
()=> [ Result = Json.Document(Web.Contents(url & path)) ],
each [Result][Next] <> null,
each [Result = Json.Document(Web.Contents(url & [Result][Next]))],
each [Result][Results]
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"_self"}, {"Column1._self"}),
#"Expanded Column1._self" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1._self", {"APIURL"}, {"Column1._self.APIURL"})
in
#"Expanded Column1._self"
Sorry I can't give you the website or password to check this... as mentioned, it's a business page and can't share this info. Thanks again for your time and helping me.
Best.
Hi @Anonymous
What does the error say?
I can't test your code but in case there's an error in the code, try this which is a little rearranged
let
url = "https://x/",
path = "api/4.2/selections/3",
Source = List.Generate (
()=> [ Result = Json.Document(Web.Contents(url & path)) ],
each [Result][Next] <> null,
each [Result = Json.Document(Web.Contents(url & [Result][Next]))]
)
in
Source
Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
Hi Philip,
The error I got with my solution is this https://photos.app.goo.gl/DaexpS7R8kGSxWBK9.
Thanks for the response. Your script provides endless number of rows https://photos.app.goo.gl/mVbzeWshJac4MW1SA.
Thanks.
Ciao
dovresti far vedere nel dettaglio il tipo di errore che risulta, come dovresti pure postare il codice che usi (magari "nascondendo" le parti sensibili).
Ad occhio, farei un tentativo sul controllo di fine iterazione:
each [Result][Next] <> null,
Se ho cpaito bene alla fine il field [Next] manca del tutto non è semplicemente null.
Prova a cambiare l'istruzione, con qualcosa del gerene:
Record.HasFields(
Ma, ripeto, è solo una congettura. Con le informazioni che hai fornito è difficile fare una diagnosi
Hi Rocco_sprmnt21,
Thanks for the help. Your answer is in Italian, so I hope Google Translator did a good job.
The error give this detail The error I got with my solution is this https://photos.app.goo.gl/DaexpS7R8kGSxWBK9. I'm afraid I did put all info I have, don't have anything else to share with except the website, which is confidential.
I'm not very skilled with M... what do you suggest for Record.hasfield? I did search on google but don't know how to make this formula for my case.
Thanks.
List.Generate ( ()=>
[Result = Json.Document(Web.Contents("x/api/4.2/selections/3"))],
each Record.HasFields([Next]),
each [Result = Json.Document(Web.Contents(Text.Combine({"https://x/api/4.2/selections/3", Text.Replace([Result][Next], "?cursor=", "/")})))]
)
Hi,
your google is no worse than mine so ... 😁
About the error message you should click on the cell where there is error to see the error log.
The sintax of record.hasfields function is here
should (if you con't share your code, we can only suppose)be like this:
each Record.HasFields(Result, "Next"),
or better
each Record.HasFields([Result], "Next"),
hahaha. Yep, same google translator hahaha
Yes, I looked at that syntax but couldn't come up with a solution...
I used your suggestion but gave me an error: An error occurred in the ‘’ query. Expression.Error: The name 'Results' wasn't recognized. Make sure it's spelled correctly.
So, I used the square brackets
List.Generate ( ()=>
[Result = Json.Document(Web.Contents("https://x/api/4.2/selections/3"))],
each Record.HasFields([Results], "Next"),
each [Result = Json.Document(Web.Contents(Text.Combine({"https://x/api/4.2/selections/3", Text.Replace([Result][Next], "?cursor=", "/")})))]
)
But gave an error
An error occurred in the ‘’ query. Expression.Error: The field 'Results' of the record wasn't found.
Details:
Result=[Record]
Thanks.
Why each Record.HasFields([Results], "Next")?
I wrote
each Record.HasFields([Result], "Next"),
wihout final s.
Yes, sorry, forgot to mention that. I tried without the "s" but gave an error:
An error occurred in the ‘’ query. Expression.Error: The name 'Result' wasn't recognized. Make sure it's spelled correctly. And if I use [Result], I get this https://photos.app.goo.gl/DaexpS7R8kGSxWBK9
When I call a page I get the field "Results" instead of "Result". That's why I used Results instead. But, either I use square brackets or not, it doesn't work.
Thanks.
"An error occurred in the ‘’ query. Expression.Error: The name 'Result' wasn't recognized. Make sure it's spelled correctly. And if I use [Result], I get this https://photos.app.goo.gl/DaexpS7R8kGSxWBK9"
1) click on the error cell to see the complete description
2) open advanced editor and copy and paste the code you use (with the omissis eventually)
Hi Rocco,
All info I get shows in the link I gave https://photos.app.goo.gl/DaexpS7R8kGSxWBK9.
So I get just one record as "Error", and If I click it, at the bottom of the picture you'll see a row with two columns: Result, Record.
The script is this
List.Generate ( ()=>
[Result = Json.Document(Web.Contents("https://x/api/4.2/selections/3"))],
each Record.HasFields([Result], "Next"),
each [Result = Json.Document(Web.Contents(Text.Combine({"https://x/api/4.2/selections/3", Text.Replace([Result][Next], "?cursor=", "/")})))]
)
As mentioned, either I use Result, [Result], Results, [Results]. Nothing works, all of them give errors I showed in previuos posts.
Thanks for the help and time.
"So I get just one record as "Error", and If I click it, at the bottom of the picture you'll see a row with two columns: Result, Record."
then the problem isn't anymore the Result parameter.
you have to click on the word error and when get the new window with Result=record click on the word record to see what contains and so on until you get the error log.
I'm sorry I can't help you more.
Hi Rocco,
If I click "Error" I get this https://photos.app.goo.gl/DsNTtW47bLjteEsRA
Then, If I click Record I get this https://photos.app.goo.gl/rQdVyxjg1VkyJoND8
Weird, I get the word "Error", but If click on that then I get the results of calling the website (first page)
Thanks.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |