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
