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.
TLDR: Stop generating list if the contents inside the list are empty. Empty lists are inside a table that has the lists.
list < table < list
Hello, I am trying to extract data from the URL, and I found that something works for me. Many thanks to Curbal's excellent video on How to do Pagination without knowing the number of pages (Part 2) in Power Query | List.Generate
Even though the script runs perfectly, the list.generate function generates rows without stopping. As you can see in the screenshot, it keeps generating even if the list contents are empty. The next page link is an indicator as well(this is an OData next page option which I am not using. I am using a page number parameter instead to get the set of records)
Please see the Power Query steps with the results
<!--
Source = List.Generate(()=>
[Result = try AuditHistoryFn(1) otherwise null, Page=1],
each [Result]<>null,
each [Result = try AuditHistoryFn([Page]+1) otherwise null, Page=[Page]+1],
each [Result])
-->
Result
Except for the list row 7, every other list does not contain any data.
The funtion that takes the page number as a parameter.
Really appreciate any guidance on this. I am not sure whether it makes sense to check the list.contains > table.contains > and again if the last list is empty to stop generating list.
Thank you.
Cheers,
BK
Solved! Go to Solution.
Hi @ImkeF,
Many thanks for all your help. However debugging the list.generate() was taking so much time I decided to take a different path and ended up coming up with the following solution which worked.
et
BaseUrl = "URL"
EntitiesPerPage = 1000,
GetJson = (Url) =>
let Options = [
Headers = [
Accept="application/json",
Prefer="odata.maxpagesize=1000"
]
],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = BaseUrl & "&$count=true",
Json = GetJson(Url),
Count = Json[#"@odata.count"]
in Count,
GetPage = (Index) =>
let Skip = "&$skiptoken=<cookie%20pagenumber=%22" & Number.ToText(Index) & "%22/>",
//Top = "$top=" & Text.From(EntitiesPerPage),
Url = BaseUrl & Skip,
Json = GetJson(Url)
//Value = Json[#"value"]
in Json,
EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 1 .. PageCount - 0 },
Pages = List.Transform(PageIndices, each GetPage(_)),
This wouldn't have been possible if not for the post by @MarioB84
Thank you.
Cheers,
BK
Hi Anonymous, I know you have set this to Solved, but I have been on a similar journey to yours, and I think I may have reached an alternative solution which is closer to Curbal's original using 'List.Generate()'.
I am working with the Atlassian Confluence Cloud api (ref: https://developer.atlassian.com/cloud/confluence/rest/v1/api-group-space/#api-wiki-rest-api-space-ge...)
The first part is to create a function which returns a table of results:
= (Page as number)=>
let
Source = Json.Document(Web.Contents("https://<site_name>.atlassian.net/wiki/rest/api/space?expand=permissions&limit=25&start="&Number.ToText(Page))),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"results", type any}, {"start", Int64.Type}, {"limit", Int64.Type}, {"size", Int64.Type}, {"_links", type any}}),
values = #"Changed Type"{0}[results],
#"Converted to Table1" = Table.FromList(values, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table1"
In my query with List.Generate(), I am then able to use this condition to test when to stop:
each Table.RowCount([Result])>0
(to my understanding, this returns FALSE when the table is empty. There may be other ways of getting the same result, but this one works for me)
It looks very simple, but it took me a few attempts to get there!
(inspiration came via this page https://www.dalesandro.net/handling-paginated-rest-api-results-with-power-query/)
let
Source = List.Generate(()=>
[Result = try fGetConfluenceDataTable(0) otherwise null, Page=0],
each Table.RowCount([Result])>0,
each [Result = try fGetConfluenceDataTable([Page]+25) otherwise null, Page=[Page]+25],
each [Result]),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Column1"}, {"Column1.1"}),
#"Expanded Column1.1" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.1", {"id", "key", "name", "type", "permissions", "status", "_expandable", "_links", "avatarUrls", "projectCategory", "projectTypeKey", "simplified", "style", "isPrivate", "properties"}, {"id", "key", "name", "type", "permissions", "status", "_expandable", "_links", "avatarUrls", "projectCategory", "projectTypeKey", "simplified", "style", "isPrivate", "properties"})
in
#"Expanded Column1.1"
best regards,
Richard
omg omg... this help me a lot.
many many tks.
Hi @ImkeF,
Many thanks for all your help. However debugging the list.generate() was taking so much time I decided to take a different path and ended up coming up with the following solution which worked.
et
BaseUrl = "URL"
EntitiesPerPage = 1000,
GetJson = (Url) =>
let Options = [
Headers = [
Accept="application/json",
Prefer="odata.maxpagesize=1000"
]
],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = BaseUrl & "&$count=true",
Json = GetJson(Url),
Count = Json[#"@odata.count"]
in Count,
GetPage = (Index) =>
let Skip = "&$skiptoken=<cookie%20pagenumber=%22" & Number.ToText(Index) & "%22/>",
//Top = "$top=" & Text.From(EntitiesPerPage),
Url = BaseUrl & Skip,
Json = GetJson(Url)
//Value = Json[#"value"]
in Json,
EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 1 .. PageCount - 0 },
Pages = List.Transform(PageIndices, each GetPage(_)),
This wouldn't have been possible if not for the post by @MarioB84
Thank you.
Cheers,
BK
Hi @ImkeF, many thanks for responding to my question. Unfortunately that doesn't seem to do the trick. The BIccountant site has a ton of amazing content 🙂 I will keep digging into this and share the solution when I figure it out
Hi @Anonymous ,
next thing I would try is to move the stop-condition into the regular results for easier debugging.
Then add a condition to run the loop 9 times and examine the results. You should see error-messaged indicating what is wrong with the stop-condition:
Source = List.Generate(()=>
[Result = try AuditHistoryFn(1) otherwise null, Page=1, PrevOData = 1, Counter = 0],
each [Counter]<9,
each [Result = try AuditHistoryFn([Page]+1) otherwise null, Page=[Page]+1],
PrevOData = {Result][@odata.nextLink],
StopCondition = [PrevOData] <> null,
Counter = [Counter] + 1
])
Make sure to expand all the columns for being able to debug
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @Anonymous ,
it is not easy to debug a List.Generate from seeing the code alone, but you could try the following:
<!--
Source = List.Generate(()=>
[Result = try AuditHistoryFn(1) otherwise null, Page=1, PrevOData = 1],
each [PrevOData]<>null,
each [Result = try AuditHistoryFn([Page]+1) otherwise null, Page=[Page]+1],
PrevOData = {Result][@odata.nextLink]
each [Result])
-->
So your function call never return an error and therefore Result will never get null.
Instead, you now drill down into the returned result instead and retrieve the previous odata.nextLink. That will hopefully keep the row number 7.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.