double format for a single column

[LukeReds]

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

[LukeReds]

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]

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(_)})

[ronrsnfld]

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]

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})

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

[LukeReds]

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

[zenisekd]

@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 · Accepted solution]

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....