Forum Discussion
Custom Colum for converting numbers to time for Query Folding
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",
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
1 Reply
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