README.md

Two of the most tedious activities in PowerQuery - renaming columns and replacing values!

These two functions let you define column name and value label mappings in seperate table!

Recommended usage is to apply BulkReplaceValues() as a late step (i.e. after filtering), and BulkRenameColumns() as a final step.

These functions are provided as-is; use at your own risk.

BulkRenameColumns.pq

let

    FnType = type function (
        DataTable as (type table meta [
            Documentation.FieldCaption = "DataTable",
            Documentation.FieldDescription = "Table that contains columns that need renaming."
        ]),
        DataMapTable as (type table meta [
            Documentation.FieldCaption = "DataMapTable",
            Documentation.FieldDescription = "Table that contains at least 2 text columns; ""Variable"" and ""Variable Label"""
        ]),
        optional prefix as (type logical meta [
            Documentation.FieldCaption = "prefix",
            Documentation.FieldDescription = "If true, the original column name will be added in square braces as a prefix to the new column name."
        ])
    ) as table meta [
        Documentation.Name = "BulkRenameColumns",
        Documentation.Description = "Applies column renames to an entire table using the supplied data map.",
        Documentation.LongDescription = "A PowerQuery function that applies column renames to an entire table using column lables that are described in a seperate data map table",
        Documentation.Examples = {[
            Description = "Typical usage",
            Code = "BulkRenameColumns(#(lf)    #table({""COL""}, {{""VAL""}}), #(lf)    #table({""Variable"", ""Variable Name"", ""Value"", ""Value Label""}, {{""COL"", ""Column"", ""VAL"", ""Value""}})#(lf))",
            Result = "#table({""Column""}, {{""VAL""}})"
        ]}
    ],

    // Bulk replace columns in DataTable
    // DataMap must contain "Variable" and "Variable Label" columns. repeated labels are fine, different labels are not.
    FnImpl = (DataTable as table, DataMapTable as table, optional prefix as logical) => 
    
        let
            ColumnMapDirty = Table.Distinct(Table.SelectColumns(DataMapTable, {"Variable", "Variable Label"})),
            
            ColumnMap = if prefix = true
                then
                    // if they want the orginal column name prefixed to the new one, do so
                    let
                        #"Inserted Merged Column" = Table.AddColumn(
                            ColumnMapDirty,
                            "Variable Label Merged", 
                            each "[" & [Variable] & "] " & [Variable Label],
                            type text
                        ),
                        #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column", {"Variable Label"}),
                        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Variable Label Merged", "Variable Label"}})
                    in
                        #"Renamed Columns"
                else
                    // otherwise the new column label we found will do fine.
                    ColumnMapDirty,
    
            Result = Table.RenameColumns(
                DataTable, 
                Table.ToRows(
                    Table.SelectRows(
                        ColumnMap,
                        each List.Contains(Table.ColumnNames(DataTable), [Variable])
                    )
                )
            )
        
        in
            Result

in
    Value.ReplaceType(
        FnImpl,
        FnType
    )

BulkReplaceValues.pq

let

    FnType = type function (
        DataTable as (type table meta [
            Documentation.FieldCaption = "DataTable",
            Documentation.FieldDescription = "Table that contains values that need replacing.  Note that any columns in this table that have replacements in the DataMapTable should be type text"
        ]),
        DataMapTable as (type table meta [
            Documentation.FieldCaption = "DataMapTable",
            Documentation.FieldDescription = "Table that contains at least 3 text columns; ""Variable"", ""Value"" and ""Value Label"".  These will be used to build the replacements."
        ]),
        optional prefix as (type logical meta [
            Documentation.FieldCaption = "prefix",
            Documentation.FieldDescription = "If true, prefixes the new values with the original ones."
        ])
    ) as table meta [
        Documentation.Name = "BulkReplaceValues",
        Documentation.Description = "Applies value labels to an entire table using the supplied data map.",
        Documentation.LongDescription = "A PowerQuery function that applies value transformations across multiple columins in a source table with value labels that are described in the data map",
        Documentation.Examples = {[
            Description = "Typical usage",
            Code = "ApplyDataMapValues(#(lf)    #table({""COL""}, {{""VAL""}}), #(lf)    #table({""Variable"", ""Variable Name"", ""Value"", ""Value Label""}, {{""COL"", ""Column"", ""VAL"", ""Value""}})#(lf))",
            Result = "#table({""COL""}, {{""Value""}})"
        ]}
    ],

    // Bulk replaces a table's worth of values using DataMap
    // DataMap must contain "Variable", "Value" and "Value Label" columns.
    FnImpl = (DataTable as table, DataMapTable as table, optional prefix as logical) => 
    
    let 
        // Group the whole table by "Variable"
        GroupedDataMapTable = Table.Group(DataMapTable, {"Variable"}, {{ "Value Map", each _ }}),

        // Create the [value=label, transform=ation] records for each column
        ValueMapsDirty = Table.TransformColumns(GroupedDataMapTable, {{ "Value Map", each 
            Record.FromList(Table.Column(_, "Value Label"), Table.Column(_, "Value"))
        }}),

        // Blatantly ignore any errors by removing them
        ValueMaps = Table.RemoveRowsWithErrors(ValueMapsDirty, {"Value Map"}),

        // List of column names in the source data that we actually have mapped values for
        IntersectingColumns = List.Intersect({
            Table.Column(ValueMaps, "Variable"),
            Table.ColumnNames(DataTable)
        }),

        // Build a list of transforms
        Transforms = List.Transform(IntersectingColumns, (ColumnName) => {
            ColumnName, 
            (DataValue) => 
                if DataValue = null then null
                else (
                    (if prefix = true then (DataValue & " - ") else (""))
                    & Record.Field(
                        ValueMaps{[Variable=ColumnName]}[#"Value Map"],
                        DataValue
                    )
                )
            }
        ),

        // Now apply the transforms to the source data and we're done!
        TransformedData = Table.TransformColumns(DataTable, Transforms)

    in
        TransformedData

in
    Value.ReplaceType(
        FnImpl,
        FnType
    )