Transforming multiple columns

[jnixon]

Greetings M Gurus,

I am trying to write a standalone function that accepts a table as a parameter and returns that table, but with every value in every date column 90 days later:   Date.AddDays(tbl[date],90).

In the function, I generate the DateColNameList, which might hold something like {“Invoice date”, “Delivery Date”, etc…}.

I can perform the transform on the first column easily enough:

ReturnTable =

    Table.TransformColumns (

        myTable,

        {DateColNameList{0}, each Date.AddDays( _, 90) }

)

But I think I need to wrap this in a List.Transform, or other Transform/Accumulate in order to cycle through the columns in DateColNameList.  Or perhaps there is an easier way?

Many thanks in advance for any help,

Jeff

[mahoneypat · Accepted solution]

List.Transform is right.  You can do it as follows:

1.  Add a step with this M code -  ColumnFunctionList = List.Transform(DateColList, each {_, each Date.AddDays(_,90)})

2. Do your transform step with - NewTable = Table.TransformColumns(MyTable, ColumnFunctionList)

That was a fun one.  Thanks.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[jnixon]

Glad you liked it!  Your solution is excellent!

Here is my (and your) function code as a general purpose date column manipulator.  I'll definitely document this pattern since it is so reusable and easily modified to work with any other type of column.   In this case, i wanted to shift the date-based columns of each table in my MS sample databases so that the dates were recent.  Mission accomplished!

Thanks again,

Jeff

(myTable) =>

let

DateColNameList = Table.ColumnsOfType ( myTable , {type nullable date} ),
//Get list of names of all columns of type nullable date

DateList = List.Transform(DateColNameList, each List.Sort( Table.Column(myTable, _) , Order.Descending) ),
//Generate list of lists, each member a column of dates

AccumOutput =
List.Accumulate (
DateList ,
{#date(1000,1,1)},
( max , current ) => if List.Max ( max ) < List.Max ( current ) then current else max
),
//Return the date list with the most recent date in it

MaxDate = List.Max( AccumOutput),
//Get the max date in the list with the most recent date of all the lists
ET = Number.RoundDown( Number.From( DateTime.LocalNow() - DateTime.From ( MaxDate ) ) ),
//Calculate the number of days from Max date to today

ColumnFunctionList = List.Transform ( DateColNameList, each {_, each Date.AddDays(_,ET)}),
//Build list of functions for second parameter in following Table.Transform() step

NewTable = Table.TransformColumns(myTable, ColumnFunctionList)
//Return new table with each date in each date column shifted so that the max date is today

in
NewTable