Custom Colum for converting numbers to time for Query Folding

[joshua1990]

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",

[QuinnP · Accepted 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