Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
LukeReds
Helper II
Helper II

double format for a single column

Hi to everyone,

in Power Query i have a column that contains either days or hours, for example

4

5:30

12:00

2

and so on (what a crazy thing i know....)

How can i properly format this column? I don't want to use "duration" format, i tryed this but it doesn't works:

 

Table.TransformColumns(PrevStep, {{"Period", each try Number.Type(_) otherwise Time.Type(_)}})

 

Thank you in advance

 

1 ACCEPTED SOLUTION
zenisekd
Super User
Super User

Hi, I assume that you dont want to summarize the column, right ? (you you do, I would suggest that you convert with conditional column everything to hours (if contains ":", then multiply by 24, and next add":00"). 

But if you want just to adjust the format in one column, you could add a calculated column (just simply formatted column = original column, which would use dynamic formatting, for which you would create a condition, that if it contains ":" then format as hours, if not, then format as days.... 

zenisekd_1-1706176684837.png

 

 

View solution in original post

7 REPLIES 7
LukeReds
Helper II
Helper II

In other words, if in excel i have this data

4

5:30

12:00

2

Suppose i just load these data in power query and then reload it in excel.

I'd like to have 2 different format in the column, number and hours, with this istruction i have it on power query but when i reload the data in excel it doesnt keep the format

 

=Table.TransformColumns(PrevStep, {"data", each try Time.From(_) otherwise Number.From(_)})

 

 

LukeReds
Helper II
Helper II

hi, i only need 2 different data formats in the same column, no sum or other.

I tried this and it functions in Power query but not when the data are loaded in excel

 

= Table.TransformColumns(PrevStep, {"Period", each try Duration.From(_) otherwise Date.From(_)})

You cannot have two different formats in the same column in Power Query. If you need each entry to appear differently, you'll have to format as a Text String and use that.

ronrsnfld
Super User
Super User

Since the integers represent Days, you cannot type it as Time as the PQ Time data type will not accept hours > 24.

 

To type it as duration, ensure that PrevStep has the column typed as Text. Then you can use the code line:

 

 #"Type as Duration" = Table.TransformColumns(PrevStep,
        {"Period", each 
            if not Text.Contains(_,":") then #duration(Number.From(_),0,0,0) else
            let 
                x = Text.Split(_,":"),
                dy = 0,
                hr =Number.From(x{0}),
                min = try Number.From(x{1}) otherwise 0,
                sec = try Number.From(x{2}) otherwise 0
            in 
                #duration(dy,hr,min,sec),
            type duration})

 

ronrsnfld_0-1706183773814.png

To represent the values in some other manner, please be specific.

 

LukeReds
Helper II
Helper II

hi zenisekd thank you for your answer but i am in Power Query, not in POwer Bi

@LukeReds You CANNOT have two datatypes in a single column. You can have text type, pretending to be hours and days (through format) depending on the row, but that is it. 
What is your desired outcome from the data you mentioned?

zenisekd
Super User
Super User

Hi, I assume that you dont want to summarize the column, right ? (you you do, I would suggest that you convert with conditional column everything to hours (if contains ":", then multiply by 24, and next add":00"). 

But if you want just to adjust the format in one column, you could add a calculated column (just simply formatted column = original column, which would use dynamic formatting, for which you would create a condition, that if it contains ":" then format as hours, if not, then format as days.... 

zenisekd_1-1706176684837.png

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors
Top Kudoed Authors