Power Query M Function to Replace Zero with Null

Overview

This function processes a table in Power Query to replace any instances of 0 in a specified column (expdate) with null. This is useful for data cleansing and ensuring that your dataset is ready for analysis without zero values affecting your calculations.

Function Definition

let
    ReplaceZeroWithNull = (inputTable as table, columnName as text) as table =>
    /*
    Replaces zero values in the specified column of a table with null.
    
    Parameters:
    - inputTable: The input table containing the data.
    - columnName: The name of the column where zeros should be replaced with null.
    
    Returns:
    - A new table with zero values in the specified column replaced by null.
    
    Exceptions:
    - Throws an error if the specified column does not exist in the input table.
    */
    let
        // Check if the column exists in the input table
        columnExists = Table.HasColumns(inputTable, {columnName}),
        
        // If the column exists, replace 0 with null; otherwise, throw an error
        resultTable = if columnExists then
            Table.TransformColumns(inputTable, 
                {columnName, each if _ = 0 then null else _}
            )
        else
            error "Column not found in the input table."
    in
        resultTable
in
    ReplaceZeroWithNull

Explanation of the Code

1. Function Declaration:

   • The function ReplaceZeroWithNull takes two parameters: inputTable and columnName.

2. Documentation Block:

   • A comprehensive docstring explains the purpose of the function, details its parameters, return type, and any exceptions raised.

3. Column Existence Check:

   • The function first checks if the specified column exists using Table.HasColumns.

4. Transform Columns:

   • If the column exists, it applies Table.TransformColumns to replace 0 with null.
   • We use a conditional expression to check each value: if _ = 0 then null else _.

5. Error Handling:

   • If the column does not exist, it raises an error indicating that the column was not found.

Usage Example

Below is an example of how to use the ReplaceZeroWithNull function within a Power Query context:

let
    // Sample input table
    SampleData = Table.FromRecords({
        [ID = 1, expdate = 0],
        [ID = 2, expdate = 5],
        [ID = 3, expdate = 0],
        [ID = 4, expdate = 10]
    }),
    
    // Calling the function to replace zeros in the 'expdate' column
    ResultTable = ReplaceZeroWithNull(SampleData, "expdate")
in
    ResultTable

Practical Considerations

• Ensure that the input data is structured correctly and the column names match expected values.
• Handle the cases when a column may not exist to maintain data integrity and avoid runtime errors.

Conclusion

This Power Query function efficiently replaces 0 values with null in specified columns, enhancing the quality of your dataset for analysis. For further enhancements and learning, consider exploring the Enterprise DNA Platform for in-depth courses on Power Query and data modeling.