Forum Discussion
Transforming multiple columns
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
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
2 Replies
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
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 dateDateList = List.Transform(DateColNameList, each List.Sort( Table.Column(myTable, _) , Order.Descending) ),
//Generate list of lists, each member a column of datesAccumOutput =
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 itMaxDate = 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 todayColumnFunctionList = List.Transform ( DateColNameList, each {_, each Date.AddDays(_,ET)}),
//Build list of functions for second parameter in following Table.Transform() stepNewTable = Table.TransformColumns(myTable, ColumnFunctionList)
//Return new table with each date in each date column shifted so that the max date is todayin
NewTable
