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.
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
But I get the error "Expression.Error: [1,1-1,20] The name 'Duration.TotalHours' doesn't exist in the current context".
Add it to the context as requested. There is an additional parameter to Expression.Evaluate().
Expression.Evaluate(Record.Field(_, "Units",[Duration.TotalHours=Duration.TotalHours])
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.