M (Power Query) Function to Set Column Values to Null

This document outlines a Power Query function that adds a new column to the oeordh table. This new column will return null if the value in the expdate column is 0. Implementation follows best coding practices including input validation and documentation.

Code Overview

The following code snippet defines the function to add the new column to the oeordh table.

let
    // Function to conditionally set values in a new column based on expdate
    AddNullIfExpDateZero = (inputTable as table) as table =>
        let
            // Validate if the input is a table
            ValidatedInput = if Table.IsEmpty(inputTable) then error "Input table cannot be empty." else inputTable,

            // Add a new column "NewColumn" based on the condition
            ResultTable = Table.AddColumn(
                ValidatedInput,
                "NewColumn",
                each if Record.Field(_, "expdate") = 0 then null else Record.Field(_, "expdate"),
                // Optional: type of new column (can be changed based on needs)
                Int64.Type
            )
        in
            ResultTable
in
    AddNullIfExpDateZero

Key Components

1. Function Definition:

   • The function AddNullIfExpDateZero accepts a parameter inputTable of type table and returns a modified table.

2. Input Validation:

   • The function checks if the input table is empty and raises an error if it is, ensuring robust input handling.

3. Adding a Column:

   • Uses Table.AddColumn to add a new column named NewColumn.
   • In each row, it checks if expdate equals 0. If true, it returns null; otherwise, it copies the value of expdate.

4. Type Declaration:

   • Specifies the type of NewColumn as Int64.Type, which can be adjusted based on the expected data type.

Commentary

• The use of Record.Field(_, "expdate") accesses the expdate field in each row efficiently and clearly.
• The inline comments clarify the purpose of each code block, adhering to best practices for readability and maintainability.

Example Usage

To use this function with an actual table, you can load your data in Power Query and apply the function as shown below:

let
    // Simulate loading data (replace with actual data loading step)
    Source = Table.FromRecords({
        [orderID = 1, expdate = 0],
        [orderID = 2, expdate = 2022],
        [orderID = 3, expdate = 0],
        [orderID = 4, expdate = 2023]
    }),

    // Applying the function to the loaded data
    Result = AddNullIfExpDateZero(Source)
in
    Result

Conclusion

This Power Query function efficiently adds a column based on the specified condition, following good coding practices in M. Implement this script to handle your data transformation needs effectively within the Power BI environment. For further refinement and learning, consider exploring additional resources on the Enterprise DNA Platform.