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"

The code works fine for 1 column (column A in the example). I need to apply it to every column in the table, however. I have the line commented out that is my attempt at doing this. So, at the end of running the code, columns A, B, and C should all have been multiplied by 100. — Zoopzoop, Mar 15 '19 at 15:40

score 3 · Accepted Answer · answered Mar 15 '19 at 16:04

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. However, 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.

Corrected Code

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"

Worked perfect, thank you so much! I really appreciate the explanation of _ as well. Helped clear up what was happening in the Record.Field function. — Zoopzoop, Mar 15 '19 at 16:25
@Zoopzoop You're welcome. The tricky bit with `_` is that it's meaning can change depending on the function it's being used with. I've had some table-level functions where `_` means a specific field instead of a record. — Mistella, Mar 15 '19 at 17:34

Power Query Applying a Function Across Every Column

1 Answers1

Linked