Power Query Function to Fetch Products from API

This documentation outlines an M (Power Query) function that retrieves product data from a specified API. If the API returns an error or no products, the function returns an empty table.

Function Overview

Function Name: GetProducts

Description

This function retrieves products from an API designed for a category search. It handles errors gracefully by checking for null responses and returning an empty table if the API fetch fails or provides no products.

Parameters

• None

Returns

• A table containing the products or an empty table if no products are found or an error occurs.

Exceptions

• Raises errors related to network issues or invalid API responses are caught and handled.

Implementation

let
    // Function to get products from API
    GetProducts = () as table =>
    let
        // API URL for fetching products
        url = "https://www.maxi.rs/api/v1/?operationName=GetCategoryProductSearch&variables=%7B%22lang%22%3A%22sr%22%2C%22searchQuery%22%3A%22%3Arelevance%22%2C%22sort%22%3A%22relevance%22%2C%22category%22%3A%2202%22%2C%22pageNumber%22%3A0%2C%22pageSize%22%3A520%2C%22filterFlag%22%3Atrue%2C%22plainChildCategories%22%3Atrue%7D&extensions=%7B%22persistedQuery%22%3A%7B%22version%22%3A1%2C%22sha256Hash%22%3A%22161b8b6137d82243a0dbfeed8477edec6469b84e16b0d00490c1133c57e3f234%22%7D%7D",
        
        // Fetch data from the specified URL
        Source = Json.Document(Web.BrowserContents(url)),
        
        // Attempt to extract products from the source
        Products = try Source otherwise null,
        
        // Check if Products is null and return an empty table if so
        ProductsList = if Products <> null then Products[products] else {},
        
        // Convert products list to a table; returns an empty table if ProductsList is empty
        ProductsTable = Table.FromList(ProductsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in 
        ProductsTable
in
    // Invoke the function to test
    GetProducts

Explanation of Key Steps

1. API URL Definition: The URL variable sets the endpoint for the API call.
2. Data Fetching: The Web.BrowserContents function fetches the data, and Json.Document parses the JSON response.
3. Error Handling: The try ... otherwise construct ensures that if an error occurs (e.g., invalid response), it falls back to null.
4. Conditional List Extraction: Checks if Products is not null; if it is, it initializes ProductsList as an empty list.
5. Table Creation: Converts the list to a table with Table.FromList. Returns an empty table if no products exist.

Code Usage Example

To utilize this function in Power Query, simply call it from any query like this:

let
    // Call the GetProducts function to fetch data
    ProductTable = GetProducts()
in
    ProductTable

This call will return a table of products from the specified API or an empty table if none are available. For additional Power Query training and resources, consider exploring the content offered by Enterprise DNA.