Dynamic M Function to Replace Blanks with Nulls ( #DataCleaning, #MCode, #M, #PowerQuery )

gistfile1.txt

let
    ReplaceBlanksWithNulls = (inputTable as table) as table =>
        let
            columnNames = Table.ColumnNames(inputTable),
            replaceBlanksInColumn = (table, columnName) => Table.ReplaceValue(table, "", null, Replacer.ReplaceValue, {columnName}),
            transformedTable = List.Accumulate(columnNames, inputTable, (state, current) => replaceBlanksInColumn(state, current))
        in
            transformedTable
in
    ReplaceBlanksWithNulls

Chat GPT 4's line-by-line explation of code above:

Let's dive into the function line by line to understand what's happening:

1. ReplaceBlanksWithNulls = (inputTable as table) as table =>:
   This line declares a function named ReplaceBlanksWithNulls that takes an inputTable of type table as its argument and returns a table.

2. let:
   The let keyword begins a block where local variables and transformations are defined.

3. columnNames = Table.ColumnNames(inputTable),:
   This line fetches the names of all columns in inputTable and stores them in the list columnNames.

4. replaceBlanksInColumn = (table, columnName) => Table.ReplaceValue(table, "", null, Replacer.ReplaceValue, {columnName}),:
   This line defines a sub-function called replaceBlanksInColumn that replaces blank values with nulls for a specified column in a given table.

   • (table, columnName): Takes a table and a column name as arguments.
   • Table.ReplaceValue(...): This is a built-in M function that replaces values in a table.
   • table, "", null, Replacer.ReplaceValue, {columnName}: Parameters for Table.ReplaceValue, specifying to replace blank strings ("") with nulls only in the column specified by columnName.

5. transformedTable = List.Accumulate(columnNames, inputTable, (state, current) => replaceBlanksInColumn(state, current)):
   This is where the core logic happens. List.Accumulate iterates through each column name in columnNames, applying the replaceBlanksInColumn function on the table.

   • columnNames: The list of column names to iterate through.
   • inputTable: The initial state of the table that will be transformed.
   • (state, current) => replaceBlanksInColumn(state, current): A lambda function that specifies what to do at each step of the iteration. It takes the current state of the table (state) and the current column name (current) as arguments, and then applies replaceBlanksInColumn to replace blanks with nulls in the current column.

6. in:
   The in keyword indicates the end of the let block and specifies what value to return.

7. transformedTable:
   The function returns the fully transformed table, which has blanks replaced with nulls in all columns.

This approach ensures that the transformation is applied across all columns of the input table. It uses the List.Accumulate function to iterate through each column, updating the table state as it goes.

I had a weird issue where NULL was stored as a string in some data I received, this would be a good solution for that too. Thanks for sharing! Darrell