Forum Discussion

LukeReds's avatar
LukeReds
Icon for Helper II rankHelper II
2 years ago
Solved

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...
Need Help
  • zenisekd's avatar
    zenisekd
    2 years ago

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

     

     

LukeReds's avatar
LukeReds
Icon for Helper II rankHelper II
2 years ago

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's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    2 years ago

    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.