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.
Good afternoon. I need help to list all the data on my website and that they have several pages.
@ImkeF can you help me again?
let
Source = Json.Document(Web.Contents("https://api.weasy.io/v1/catalog?X-API-KEY=<key>")),
ToTable = Record.ToTable(Source), //
Result = Table.RemoveLastN(ToTable,1),
NextPage = Source[paging][next],
Custom1 = List.Generate( () =>
[ResultRecord = Source, NextPage = NextPage],
each [NextPage] <> null,
each [
NextPage = [ResultRecord][paging][next],
ResultRecord = Json.Document(Web.Contents(NextPage)),
Result = Table.RemoveLastN(Record.ToTable(ResultRecord),1)
]
// ,each [Result]
),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Result"}, {"Result"}),
#"Expanded Result" = Table.ExpandTableColumn(#"Expanded Column1", "Result", {"Name", "Value"}, {"Name", "Value"}),
Append = Result & #"Expanded Result",
#"Filtered Rows" = Table.SelectRows(Append, each ([Name] <> null))
in
#"Filtered Rows"
Solved! Go to Solution.
Based on the information you provided here is one possible implementation. It is not optimized (page 1 is fetched twice) but it works.
You need two parameters - APIKey and Limit . The Limit parameter allows you to control how many items you want to retrieve in each web call.
let
Source = Json.Document(Web.Contents("https://api.weasy.io/v1/catalog?X-API-KEY=" & APIKey & "&limit=" & Limit)),
pages = {1..Source[paging][totalpages]},
#"Converted to Table" = Table.FromList(pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Data", each Json.Document(Web.Contents("https://api.weasy.io/v1/catalog?X-API-KEY=" & APIKey & "&limit=" & Limit & "&page=" & [Column1]))),
#"Expanded Data" = Table.ExpandRecordColumn(#"Added Custom", "Data", {"items"}, {"items"}),
#"Expanded items" = Table.ExpandListColumn(#"Expanded Data", "items")
in
#"Expanded items"
This line
Result = Table.RemoveLastN(ToTable,1),
is not used subsequently. Is that intentional?
Without details on how your API's pagination works it is hard to give you advice.
One (or all) of these:
- the API's documentation
- the output of the first call
- a sample API key
Based on the information you provided here is one possible implementation. It is not optimized (page 1 is fetched twice) but it works.
You need two parameters - APIKey and Limit . The Limit parameter allows you to control how many items you want to retrieve in each web call.
let
Source = Json.Document(Web.Contents("https://api.weasy.io/v1/catalog?X-API-KEY=" & APIKey & "&limit=" & Limit)),
pages = {1..Source[paging][totalpages]},
#"Converted to Table" = Table.FromList(pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Data", each Json.Document(Web.Contents("https://api.weasy.io/v1/catalog?X-API-KEY=" & APIKey & "&limit=" & Limit & "&page=" & [Column1]))),
#"Expanded Data" = Table.ExpandRecordColumn(#"Added Custom", "Data", {"items"}, {"items"}),
#"Expanded items" = Table.ExpandListColumn(#"Expanded Data", "items")
in
#"Expanded items"
Hi @lbendlin ,
I need your help again.
I already have more than 5000 products on the site. The problem is that when obtaining the data, the error message "(429): Too Many Requests" is returned. The site only allows 100 requests per minute. How do I get around this problem?
thank you
As always Chris Webb has the answer:
https://blog.crossjoin.co.uk/2015/04/30/using-function-invokeafter-in-power-query/
Is perfect! It worked the first time, it has no errors and it was extraordinary support. Thank you very much.
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 |
---|---|
13 | |
2 | |
2 | |
1 | |
1 |