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!

Anonymous's avatar
Anonymous
Not applicable
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!