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.
Hello everybody!
I am currently trying to optimize my query using Query Folding.
As I understand it, a custom column can also be query folding. I would like to implement this for the following:
I have two columns ("END", "BEGIN"), which contain the following values, among others:
0
233000
53000
3000
I would like to convert these into a time format.
A 0 turns into a 00:00:00.
53000 will be 05:30:00.
3000 will be 00:30:00
There is a special feature here.
240000 (only in "END") does not become 24:00:00, but 23:59:59, because there is no 24:00:00 in Power Query.
How can this be implemented with a custom column?
In SQL I have the following approach:
CASE WHEN "END" = 240000 THEN '23: 59: 59 ' ELSE Replace (To_char ("END", '00, 00,00 '),', ',': ') END AS "End", Replace (To_char ("BEGIN", '00, 00,00 '),', ',': ') AS "Start",
Solved! Go to Solution.
Hi @joshua1990 Josh,
I was able to implement this by adding a custom column with the definition below, then replacing the value "24:00:00" with "23:59:59". Note that the if statements are there to ensure that we have two zeroes "00" when we have a round number of minutes or seconds instead of a single zero.
Number.ToText(Number.RoundDown([END]/10000))&":"&(if Number.ToText(Number.Mod([END],10000)/100) = "0" then "00" else Number.ToText(Number.Mod([END],10000)/100) )&":"&(if Number.ToText(Number.Mod([END],100)) = "0" then "00" else Number.ToText(Number.Mod([END],100)) )
Cheers,
Quinn
Hi @joshua1990 Josh,
I was able to implement this by adding a custom column with the definition below, then replacing the value "24:00:00" with "23:59:59". Note that the if statements are there to ensure that we have two zeroes "00" when we have a round number of minutes or seconds instead of a single zero.
Number.ToText(Number.RoundDown([END]/10000))&":"&(if Number.ToText(Number.Mod([END],10000)/100) = "0" then "00" else Number.ToText(Number.Mod([END],10000)/100) )&":"&(if Number.ToText(Number.Mod([END],100)) = "0" then "00" else Number.ToText(Number.Mod([END],100)) )
Cheers,
Quinn
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 |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |