Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Herkatus
New Member

How to recursevely call an api with parameter from output?

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

1 REPLY 1
lbendlin
Super User
Super User

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...

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.