Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
49 | |
19 | |
12 | |
11 |