Power Query Applying a Function Across Every Column

Question

I am trying to write a query that takes a table and multiplies every number in the table by 100. I've gotten close, but I am having trouble applying it correctly to every column. Below is the code I have so far. The line starting with ReplaceTable is the line I have working for one column, and the line below was my attempt at getting it to work for other columns. I am dealing with a small subset currently, but the real data will potentially have ~100 columns, so I do not want to do this by hand. If there's a better way to do this task, please let me know. I am new to Power Query, so if able please explain my error/the solution so I can learn. Thanks!

let

    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],

    //Organization will always be of type text.  The others will be should be numbers, unless user error

    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Organization", type text}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),

   //function to replace all values in all columns with multiplied values

   MultiplyReplace = (DataTable as table, DataTableColumns as list) =>

     let

        Counter = Table.ColumnCount(DataTable),

        ReplaceCol = (DataTableTemp, i) =>

            let

                colName = {DataTableColumns{i}},

                col = Table.Column(DataTableTemp, colName),

                //LINE THAT WORKS- want this functionality for ALL columns

                ReplaceTable = Table.ReplaceValue(DataTableTemp, each[A], each if [A] is number then [A]*100 else [A], Replacer.ReplaceValue, colName)

                //ReplaceTable = Table.ReplaceValue(DataTableTemp, each col, each if col is number then col*100 else col, Replace.ReplaceValue, colName)

            in

                if i = Counter-1 then ReplaceTable else @ReplaceCol(ReplaceTable, i+1)

     in

        ReplaceCol(DataTable, 0),

    allColumns = Table.ColumnNames(#"Changed Type"),

    #"Multiplied Numerics" = MultiplyReplace(#"Changed Type", allColumns)

    //#"Restored Type" = Value.ReplaceTypes(#"Multiplied Numerics", #"Changed Type")

in

    #"Multiplied Numerics"

Answer 1

• The issue involves the scope of the functions and the variables.

• With a hard-coded column name (such as [A]), the code is understanding the shorthand to actually mean _[A]. Within a Table.ReplaceValue function, that _ is referencing the current Record or row. As, the col variable is referencing the entire table column. When used in the replacer function, it causes an error. (Unfortunately(?), errors in replacer functions are just ignored with no error message, so issues can be hard to trace.)

• In the corrected code, I got rid of the col variable, since it's being determined at the wrong scope level. I changed colName to being text instead of a list, and then used the Record.Field function with _ (the current record within the Table.ReplaceValue function) and the text value colName to extract the desired record for the calculations with the Table.ReplaceValue function itself.

let

    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],

    //Organization will always be of type text.  The others will be should be numbers, unless user error

    #"Changed Type" = Table.TransformColumnTypes(Source, {{"Organization", type text}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),

   //function to replace all values in all columns with multiplied values

   MultiplyReplace = (DataTable as table, DataTableColumns as list) =>

     let

        Counter = Table.ColumnCount(DataTable),

        ReplaceCol = (DataTableTemp, i) =>

            let

                colName = DataTableColumns{i},

                //LINE THAT WORKS- want this functionality for ALL columns

                ReplaceTable = Table.ReplaceValue(DataTableTemp,each Record.Field(_, colName), each if Record.Field(_, colName) is number then Record.Field(_, colName)*100 else Record.Field(_, colName),Replacer.ReplaceValue,{colName})

                //ReplaceTable = Table.ReplaceValue(DataTableTemp, each col, each if col is number then col*100 else col, Replace.ReplaceValue, colName)

            in

                if i = Counter-1 then ReplaceTable else @ReplaceCol(ReplaceTable, i+1)

     in

        ReplaceCol(DataTable, 0),

    allColumns = Table.ColumnNames(#"Changed Type"),

    #"Multiplied Numerics" = MultiplyReplace(#"Changed Type", allColumns)

    //#"Restored Type" = Value.ReplaceTypes(#"Multiplied Numerics", #"Changed Type")

in

    #"Multiplied Numerics"