Skip to main content
Visit Freshworks.com Visit Freshworks

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. 

After 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"  

Reply


Terms of Use