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.
I have this API call. There's pagination and a counter. But Ideally I would like it to stop whenever there's no results left. I've tried a couple of things and I guess it needs to be done during the List.Generate where I currently have each [Counter] < 50. But I`m not sure what to put there. I.e. each [WebCall] <> null ?
This is the code I have
Let Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value each [Counter]<50, each [ WebCall = Function.InvokeAfter( ()=>Json.Document(Web.Contents(Url, [Query=[limit="" & Limit & "",page="" & Text.From([Page]) & "", #"filter[1][attribute]"="created_at", #"filter[1][gt]"=StartDate], Headers=[Authorization="OAuth oauth_consumer_key=" & consumerKey & ", oauth_token=" & Token & ", oauth_signature_method=" & SignatureMethod & ", oauth_timestamp=" & TimeStamp & ", oauth_nonce=" & Nonce & ", oauth_version=""1.0"", oauth_signature=" & Signature & " "] ])), #duration(0,0,0,0.5)), Page = [Page]+1, Counter = [Counter]+1 ] ) ,1), Table = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error), column = Table.ExpandRecordColumn(Table, "Column1", {"WebCall"}, {"Column1.WebCall"}), // convert EACH record to the table of columns Name and Value^ RecordsToTables = Table.TransformColumns( column, {"Column1.WebCall", Record.ToTable} ), // expand these tables ExpandTables = Table.ExpandTableColumn(RecordsToTables, "Column1.WebCall", {"Name", "Value"}, {"WebCall.Id", "Value"}), // get all field names of all inner records of Value column: Recs = List.Buffer(ExpandTables[Value]), FieldNames = List.Union(List.Transform(Recs, Record.FieldNames)), // expand these records with the full list of field names: ExpandRecords = Table.ExpandRecordColumn(ExpandTables, "Value", FieldNames, FieldNames),
in ExpandRecords
@aukev ,
You may take a look at the post below.
Thanks, I`ll look into it.
@v-chuncz-msft I looked at the resources but sadly these won't help me. These use functions and my query should be able to be used on power bi service.
Would it work to use something like Record.Count like this?
each [Counter]<50 or Record.Count[xxxx] > 1
The problem is that I wouldn't know how to reference the records within each WebCall and check to see if they have results in them. The record I would need to check is also two levels deep due to the pagination.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |