none
using relative column references in column math functions RRS feed

  • Question

  • Hi,

    I have a question relating to using relative column references in conjunction with mathematical operations on each row of a given pair of columns.  While someone else posted that using the following syntax would work:

    = Table.TransformColumnTypes(Data0,{{Table.ColumnNames(Data0){1}, type number}})

    ...the problem is that it results in a list, as opposed to working purely as a way to reference a column by, well, reference.

    I'm trying to calculate growth rates for values in separate columns of a table imported via Power Query.  A few issues:

    - The number of columns will change between different iterations of the same file.  For example, a column will be added to the file every month.  Older columns aren't deleted.

    - The mathematical operators in the Table.AddColumn function don't work with lists as opposed to tables. Therefore, the reference method listed above doesn't work for queries like this:

    #"Inserted Addition" = Table.AddColumn(#"Changed Type", "Addition", each [#"2017-11"] + [#"2017-12"], Int64.Type)

    - I also need to calculate these growth rates for every pair of relevant columns in the table, and I can't think of a way to do this iteratively, especially since the total number of columns will keep increasing every month.

    - I also found a method for renaming all of the columns with predefined numbers, so that "hardcoded" formulas referring to columns by name would work.  The two problems with this: I need to calculate growth rates for all columns, which means that even hardcoding calculations wouldn't work, and even if I were willing to forego that requirement, I would need a way to name the columns in reverse order, so I wouldn't have to worry about the last N columns (assuming I was willing to work with the first 3, 6, or 9 columns only, for example) due to the fact that I can't specify what the last column index would be (it keeps changing).

    BTW, the growth rate calc is: (ColumnB-ColumnA)/ColumnA

    Any insight into how to get past these issues would be greatly appreciated!

    The table looks like this:

    2017-04 2017-05
    1311444 1288484
    70879 69221
    16784 16679
    34490 33944
    16172 16268
    24353 23607

    Monday, January 22, 2018 9:02 PM

Answers

  • Hi there. Try adding a custom column with the following formula:

    let colNames = List.LastN(Table.ColumnNames(PreviousStepName), 2), colA = Record.Field(_, colNames{0}), colB = Record.Field(_, colNames{1}) in (colB - colA)/colA

    This gets the last two column names from the table, and then uses them to do the calculation you described above.

    Ehren

    Monday, January 29, 2018 9:17 PM
    Owner