Forum Discussion
Transforming multiple columns
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
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
