Table.TransformColumns with reference to another column?

[jd2016]

Hello,

I'm relatively new to PQ and have been struggling with something this week that I can't seem to wrap my head around. I have economic data (GDP, PCE, etc) for a group of countries in local currency that I need to scale and round so that it is in a readable format. I have a column of country names and then columns titled with the year. If the value in the reference year (in this case, 2006) is greater than 10^7, I'd like to divide the whole series by 10^6 and round it to three decimal places. If it's less than 15000, I just want to round it. If it falls between, divide by 1000 and round to three decimal places.

I'll start by saying that I have achieved my goal by creating a new custom column for each year of data as follows:

#"2000*" = Table.AddColumn(#"Previous Step", "2000*", each if [2006]<15000 and [2000]>1 then Number.Round([2000],3)
else if [2000]>10000000 then Number.Round([2006]/1000000,3)
else Number.Round([2000]/1000,3)),
#"2001*" = Table.AddColumn(#"2000*", "2001*", each if [2006]<15000 and [2001]>1 then Number.Round([2001],3)
else if [2006]>10000000 then Number.Round([2001]/1000000,3)
else Number.Round([2001]/1000,3)),
#"2002*" = Table.AddColumn(#"2001*", "2002*", each if [2006]<15000 then Number.Round([2002],3)
else if [2006]>10000000 then Number.Round([2002]/1000000,3)
else Number.Round([2002]/1000,3)),

etc. and then delete the old columns.

This works, but I'd like something less cumbersome. Next year I will have a new year of data and I'd like to not have to go through each of my queries and add these lines individually.

I managed to do this in a smaller step with Table.TransformColumns except for one small flaw: I can't find a way to use the reference year to determine how the scaling should be applied. This is imperative, since it's time series data and every year must be scaled in the same way. Is there a way to reference another column while using Table.TransformColumns?

The step I used was this:
#Scale&Round = Table.TransformColumns(#"Previous Step", {{"2000", each if _<15000 and _>1 then Number.Round(_,3) else if _>10000000 then Number.Round(_/1000000) else Number.Round(_ / 1000,3), type number},
{"2001", each if _<15000 and _>1 then Number.Round(_,3) else if _>10000000 then Number.Round(_/1000000) else Number.Round(_ / 1000,3), type number},})
(with a line for each year of data, not copied here for space)

As an aside, I'd also love it if I could find a way to set this or the custom columns generated above to loop through every column with a 20XX format so I don't have to edit the code each year.

Thanks!

 

[MarcelBeug]

No, you can't reference another column with Table.TransformColumns.
There are possibilities if the column is a list of nested lists, records or tables, then you can do anything within the nested list, record or table.

I do have another solution for you though.
The code below adds columns to the table (in a loop for each "20xx" column, using List.Accumulate) with the calculation as specified
(please check, as I suspect some mistakes in your examples, e.g. the second line has "2000" and "2006" switched, and there is no check if the 2002 value is > 1)
Then there is a dynamic remove / rename of columns.

let
    Source = Input,
    RefYear = "2006",
    Columns20xx = List.Select(Table.ColumnNames(Source), each Text.Start(_,2) = "20" and Text.Length(_) = 4),
    Magic = 
        List.Accumulate(Columns20xx,Source, (s, c) => Table.AddColumn(s, c&"*", 
        each if Record.Field(_,RefYear) < 15000 and Record.Field(_,c) > 1 then Number.Round(Record.Field(_,c),3) else 
             if Record.Field(_,RefYear) > 10000000 then Number.Round(Record.Field(_,c)/1000000,3) else
             Number.Round(Record.Field(_,c)/1000,3))),
    RemoveColumns = Table.RemoveColumns(Magic,Columns20xx),
    RenameList = List.Transform(Columns20xx, each {_&"*", _}),
    RenameColumns = Table.RenameColumns(RemoveColumns,RenameList)
in
    RenameColumns

 

[jd2016]

This worked great. I knew I was bound to make mistakes entering each step individually, that's part of why I was hoping there was a way to automate it. Thanks so much!