Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Identify format type in if formula

Hello, I have an imported column from Excel that contains 2 different data types and I want to convert them into decimal hour. My idea is to use a if function to identify the data type and apply a ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    In Power BI, data in a column should have only one data type. So when you load this data from Excel into Power BI, you'd better keep the original column as Text type. Then add a new column by identifying whether a data has a specific symbol like ":" to identify its format. Then make different calculations accordingly. 

    For example, this is a DAX solution:

    Column =
IF (
    CONTAINSSTRING ( 'Table'[Original column], ":" ),
    VALUE ( LEFT ( 'Table'[Original column], 2 ) ),
    VALUE ( 'Table'[Original column] ) * 24
)

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

vicky_'s avatar
vicky_
Icon for Super User rankSuper User
2 years ago

There is no real way to detect the data type in Power Query, but what you can do is to try and convert your String to a date datatype, and catch errors to handle (this isn't best practice, but it works). Here is an article that might help you generate some ideas on how to do that: https://learn.microsoft.com/en-us/power-query/error-handling