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 have inherited a modle with a huge number of times hh:mm:ss and think it the precision is uneccessary and causing a lot of additional strain. Is there an easy way to reformat these as hh:mm in Power Q?
Thanks
Mike
Solved! Go to Solution.
In the current column, it will only change display
create a new column
new column = format(table[time],"HH:MM") and change data time
or
new column = time(hour(table[time]),minute(table[time]),0)
In the current column, it will only change display
create a new column
new column = format(table[time],"HH:MM") and change data time
or
new column = time(hour(table[time]),minute(table[time]),0)
Great presumably this just tuncated so 14:30:44 becomes 14:30? Is there an equivalent to rounding so it became 14:31?
Thanks
Mike
Hi,
Please try this calculated column:
Column = IF(SECOND('Test'[Time])<=30,FORMAT('Test'[Time],"HH:MM"),FORMAT(TIME(HOUR('Test'[Time]),MINUTE('Test'[Time])+1,0),"HH:MM"))
And it shows:
Best Regards,
Giotto Zhi
Do you have an M equivalent as trying to reduce the cardinality in the model so needs to be done in the query?
Thanks
@masplin wrote:
Do you have an M equivalent as trying to reduce the cardinality in the model so needs to be done in the query?
To convert the DAX formula with the IF to a similar M expression you could do the following
= if Time.Second([Custom]) > 30
then #time(Time.Hour([Custom]), Time.Minute([Custom])+1, 0)
else #time(Time.Hour([Custom]), Time.Minute([Custom]),0)
In theory I figured that the first expression would be much more expensive due to casting to and from a string. But I did some rough benchmarking and it is slower than the #time constructor, but only by about 5% in my tests.
You could add a new custom column with the following formula
Time.FromText( Time.ToText([Column1],"HH:mm"))
Then delete the original column and rename this new column
Actually I think the following expression is probably better than my previous suggestion converting to and from a string:
#time(Time.Hour([Column1]), Time.Minute([Column1]),0)
(I got this suggestion from Imke at https://thebiaccountant.com )
why is this better as your first suggestion seeme dfine?
Thanks
Mike
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |