M (Power Query) Function to Fetch Products from API

This guide provides a comprehensive function in M (Power Query) that fetches product data from a specified API endpoint using an HTTP GET request. The extracted product data is then transformed into a table format for further use in Power BI or Excel.

Function Overview

The following M code does the following:

1. Sends a request to the API to retrieve data in JSON format.
2. Parses the JSON response to extract product information.
3. Converts the product data into a table format.

Code Implementation

let
    // Function to fetch and process product data from the given URL
    FetchProducts = (url as text) as table =>
    let
        // Validate URL format
        ValidatedURL = if Text.StartsWith(url, "http") then url else error "Invalid URL format",
        
        // Fetch JSON data from the API
        Source = Json.Document(Web.BrowserContents(ValidatedURL)),
        
        // Navigate to the products in the JSON structure
        Products = Source[data][categoryProductSearch][products],
        
        // Convert the list of products into a table format
        ProductsTable = Table.FromList(Products, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

    in 
        ProductsTable
in
    FetchProducts

Key Concepts

1. URL Validation

• Ensures that the provided URL begins with "http" to prevent erroneous API calls.

2. JSON Document Parsing

• Uses Json.Document to convert the response into a M-compatible data structure.

3. Table Creation

• Employs Table.FromList to transform the list of products into a more usable table format.

Usage Example

To utilize this function in your Power Query:

1. Open Power BI or Excel's Power Query Editor.
2. Create a new blank query.
3. Paste the code into the advanced editor.
4. Call the function as follows:

let
    // Define the URL for the API request
    apiUrl = "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",
    
    // Call the FetchProducts function to get the product data in table format
    ProductTable = FetchProducts(apiUrl)
in
    ProductTable

Conclusion

This M code snippet provides a robust method for fetching and processing product data from a specified API. It incorporates error handling and transforms the data into a useful format. For additional learning on Power Query, consider exploring courses available on the Enterprise DNA Platform.