cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joshua1990 Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Highlighted
QuinnP Frequent Visitor
Frequent Visitor

Re: Custom Colum for converting numbers to time for Query Folding

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
Highlighted
QuinnP Frequent Visitor
Frequent Visitor

Re: Custom Colum for converting numbers to time for Query Folding

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 435 members 4,125 guests
Please welcome our newest community members: