I would like to dynamically evaluate hours or minutes from durations. I have successfully used a 'dictionary' table to describe data types in a master data table, e.g.
| Column Name | Type | Units |
| Start Date | type date | |
| Temperature | type number | °C |
| Pressure | type number | barg |
| Long Duration | type duration | hours |
| Short Duration | type duration | minutes |
| Comments | type text | |
I can then use
#"Added Evalaution" = Table.AddColumn(<Dictionary>, "Evaluate", each Expression.Evaluate(Record.Field(_, "Type")))
and
#"List of Transforms" = Table.ToRows(#"Added Evaluation")
to generate a list that can then be passed to
#"Changed Types" = Table.TransformColumns(<Master>, #"List of Transforms")
This will dynamically change the column types based on the 'Type' column in the dictionary.
What I would now like to do is dynamically take the 'hours' and 'minutes' from the units column (converted to 'Duration.TotalHours' and 'Duration.TotalMinutes' as strings) and use them as a transform function. I have tried
#"Added Duration Evaluation" = Table.AddColumn(#"Previous Step", "Duration", each Expression.Evaluate(Record.Field(_, "Units")))
z
But I get the error "Expression.Error: [1,1-1,20] The name 'Duration.TotalHours' doesn't exist in the current context".
Specifically, I need this to run in the service, not desktop, and I want to chart the numbers with human-readable values so I can't just use duration data types.
Edit 1: I know I can brute force it by multiplying my duration (which is essentially a fraction of a day) by either 24 for hours or 24*60 for minutes but I think I'd have to stuff this in a loop of columns and it's not very elegant
Edit 2: Not perfect but I have taken the approach described in Power Query: Data Transformation of Dynamic Columns
// Transform those columns flagged as hours to Duration.TotalHours
Hours = Table.TransformColumns (
#"Changed Types",
List.Transform (
Table.SelectRows(<dictionary>, each [Units] = "hours")[Column Name],
each {_, Duration.TotalHours, type number}
)
)
And of course, the same for [Units] = "minutes" to Duration.TotalMinutes
