M (Power Query) Function to Fetch and Format Products

The following code defines a clean and efficient function to fetch product data from a specified API and handle potential errors gracefully. This ensures the code does not break unexpectedly and is easier to debug.

Key Sections

• Imports: Although M does not require explicit import statements like other programming languages, the relevant libraries are inherent to Power Query, particularly for JSON processing and web fetching.

• Function Definition: The function GetProducts retrieves product data from the specified URL, validates the response, and formats it into a usable table.

• Error Handling: Enhance robustness by checking if data exists before attempting to process it.

M Function Code

let
    // Function to get products from API
    GetProducts = () as table =>
    let
        // API URL
        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 URL
        Source = Json.Document(Web.BrowserContents(url)),
        
        // Validate and extract products
        Products = try Source[data][categoryProductSearch][products] otherwise null,
        
        // Check if Products is null and return an empty table if so
        ProductsList = if Products <> null then Products else {},
        
        // Convert products list to table
        ProductsTable = Table.FromList(ProductsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in 
        ProductsTable
in
    GetProducts

Code Description

1. GetProducts Function:

   • Returns a table of products fetched from the specified API.

2. API URL:

   • Defined as url, where the request parameters are set based on the requirement.

3. Web Request:

   • Uses Web.BrowserContents(url) to make the request and fetch data.

4. Error Handling:

   • The try ... otherwise construct is used to gracefully handle scenarios where data retrieval may fail.

5. Data Processing:

   • Checks if Products is valid, and if not, it initializes it to an empty list to prevent errors during table creation.

6. Table Generation:

   • Converts the list of products into a table format suitable for further analysis.

Usage Example

To use the GetProducts function in Power Query, follow these steps:

1. Open Power Query Editor in your environment (like Excel or Power BI).
2. Create a new blank query.
3. Copy and paste the entire function code into the Advanced Editor.
4. Call the function to fetch product data by simply entering:

let
    // Invoke the GetProducts function
    ProductsTable = GetProducts()
in
    ProductsTable

This setup ensures you have a neatly formatted table of products fetched from the URL, ready for any further transformations or analysis you may want to perform.

Conclusion

This M function illustrates best practices in error handling, data validation, and succinct coding. It can seamlessly integrate into your existing Power Query tasks, saving time while maintaining code quality and stability. For enhanced skills in Power Query or M programming, consider engaging with courses offered by the Enterprise DNA Platform.