Power Query Function to Replace Zero with Null

This document outlines a Power Query function that utilizes the Table.AddColumn method to return null for zero values in the expdate column of the oeordh table.

Objective

To create a new column in the oeordh table that replaces zero values in the expdate column with null, while retaining all other values.

Code Implementation

Function Definition

let
    // Function to replace zero values inexpdate column with null
    ReplaceZeroWithNull = (inputTable as table) as table =>
    let
        // Validate input type
        _ = if not Table.Is(inputTable, type table) then error "Input must be a table",

        // Add a new column based on the condition
        ResultTable = Table.AddColumn(
            inputTable,
            "ModifiedExpDate",
            each if [expdate] = 0 then null else [expdate],
            type nullable number // Specifies that the new column will accept nulls
        )
    in
        ResultTable
in
    ReplaceZeroWithNull

Explanation of the Code

1. Function Definition: The function ReplaceZeroWithNull takes one parameter inputTable, which should be of type table.

2. Input Validation: We ensure that the input is indeed a table. If not, an error is raised.

3. Adding a New Column:

   • Table.AddColumn is invoked to create a new column named ModifiedExpDate.
   • A conditional expression checks whether each value in the expdate column is equal to zero.
     • If true, null is returned.
     • Otherwise, the original value of expdate is retained.

4. Return Value: The function returns the modified table with the new column.

Code Usage Example

To use this function with the oeordh table, you would call it as follows:

let
    // Example Input Table
    oeordh = Table.FromRecords({
        [OrderID=1, expdate=0],
        [OrderID=2, expdate=5],
        [OrderID=3, expdate=0],
        [OrderID=4, expdate=3]
    }),

    // Call the function
    ModifiedTable = ReplaceZeroWithNull(oeordh)
in
    ModifiedTable

Expected Output

The execution of the above code will produce the following table:

Summary

This Power Query function efficiently generates a new column replacing zero values with null, allowing for better data representation without altering the original dataset. By following best practices in M coding, it ensures type safety and input validation, suitable for scalable data transformation tasks. For more advanced training in M coding and scenarios, consider exploring resources from the Enterprise DNA Platform.