How to recursevely call an api with parameter from output?

[Herkatus]

So, I'm trying to retrieve data from my ERP using Power Query.

With this code I'm able to retrieve the ProductCategories for parent_id 0. 

let

    // Define parameters
    your_developer_id = "your_developer_id_here",
    your_client_secret_code = "your_client_secret_code_here",
    your_user_username = "your_user_username_here",
    your_user_password = "your_user_password_here",
    company_id = 1,
	category_id = 0,
    
    // Construct the access token URL
    access_token_url = "https://api.moloni.pt/v1/grant/?grant_type=password" & 
                       "&client_id=" & your_developer_id & 
                       "&client_secret=" & your_client_secret_code & 
                       "&username=" & your_user_username & 
                       "&password=" & your_user_password,
    
    // Fetch the access token
    access_token_response = Json.Document(Web.Contents(access_token_url)),
    current_access_token = access_token_response[access_token],

    url = "https://api.moloni.pt/v1/productCategories/getAll/?access_token=" & current_access_token,
     request_body = [
        company_id = Number.ToText(company_id)
        ,parent_id = Number.ToText(category_id)
    ],
    
    // Make the request using Web.Contents
    response = Web.Contents(url, [
        Content = Text.ToBinary(Uri.BuildQueryString(request_body)),
        Headers = [
            #"Content-Type" = "application/x-www-form-urlencoded"
        ],
        ManualStatusHandling = {400, 401, 403, 404, 500, 502, 503, 504}
    ]),
    
    // Decode the JSON response
    json_response = Json.Document(response),
    
    // Check for errors in the response
    success = if Record.HasFields(json_response, {"error"}) then false else true,
    result = if success then json_response else Record.Field(json_response, "error")

in
    json_response

 However, some categories are also parent_id of other categories and I need to be able to export all into a table so I can use the category_id so I can request product data.

I've been using ChatGPT to move on with coding but I'm always getting errors like:

... 

   // Define the function to get categories
    GetCategories = (parent_id as text) =>
    let
        categories_url = "https://api.moloni.pt/v1/productCategories/getAll/?access_token=" & current_access_token,
        request_body = [
            company_id = Number.ToText(company_id),
            parent_id = parent_id
        ],

        // Make the request using Web.Contents
        categories_response = Web.Contents(categories_url, [
            Content = Text.ToBinary(Uri.BuildQueryString(request_body)),
            Headers = [
                #"Content-Type" = "application/x-www-form-urlencoded"
            ],
            ManualStatusHandling = {400, 401, 403, 404, 500, 502, 503, 504}
        ]),

        // Decode the JSON response
        categories_data = Json.Document(categories_response),

        // Check for errors in the response
        success = if Record.HasFields(categories_data, {"error"}) then false else true,
        result = if success then categories_data else Record.Field(categories_data, "error")
    in
        result,
		

    // Function to recursively get subcategories
    GetSubcategories = (category as record) =>
    let
        subcategories = GetCategories(Text.From(category[category_id])),
        subcategories_with_subcategories = List.Transform(subcategories, each GetSubcategories(_))
    in
        Record.AddField(category, "Subcategories", subcategories_with_subcategories),

    // Get root categories
    root_categories = GetCategories("0"),

    // Apply GetSubcategories function to root categories
    categories_with_subcategories = List.Transform(root_categories, each GetSubcategories(_))
in
    
	categories_with_subcategories

gets "Error: We cannot convert a value of type List to type Record."

Is it possible to achieve it with power query? 

Thanks in advance

[lbendlin]

Possible? Yes.  Recommended? No.

THere are lots of examples on the interwebs, like this: https://community.fabric.microsoft.com/t5/Community-Blog/Solving-Real-Life-Problems-with-Recursive-F...