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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors