Forum Discussion
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
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....
7 Replies
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....hi zenisekd thank you for your answer but i am in Power Query, not in POwer Bi
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.
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.
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(_)})
