cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joshua1990
Post Prodigy
Post Prodigy

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

 

1 ACCEPTED SOLUTION
QuinnP
Advocate II
Advocate II

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

View solution in original post

1 REPLY 1
QuinnP
Advocate II
Advocate II

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

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors