I am trying to export all products from FreshService to excel using Power Query I can get the first page but we have more than 100 products to return but I am new to Power Query and I cannot find an example of a method to retrieve all the pages until I reach the last one that works with the data from Freshservice. Can anyone point me in the way of a method that would work.
Best answer by pjg_west
View originalAfter no response here and a lot of googling I asked ChatGPT and it provided me the below solution, it may be of use to someone else….
let
getPage = (page as number) =>
try Json.Document(
Web.Contents(
"https://XXXXXX.freshservice.com/api/v2/products?per_page=100&page=" & Text.From(page),
[Headers=[Authorization="Basic XXXXXXXXXX"]]
)
) otherwise null,
pageList = List.Generate(
() => 1,
each not List.IsEmpty(getPage(_)[products]),
each _ + 1,
each getPage(_)
),
#"Converted to Table" = Table.FromList(pageList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"products"}, {"products"}),
#"Expanded products" = Table.ExpandListColumn(#"Expanded Column1", "products"),
#"Expanded products1" = Table.ExpandRecordColumn(#"Expanded products", "products", {"id", "created_at", "updated_at", "name", "manufacturer", "asset_type_id", "description", "description_text", "depreciation_type_id", "status", "mode_of_procurement"}, {"id", "created_at", "updated_at", "name", "manufacturer", "asset_type_id", "description", "description_text", "depreciation_type_id", "status", "mode_of_procurement"})
in
#"Expanded products1"
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
