Forum Discussion
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 formula depending on the data type.
The table below shows the different type and the expected result:
| Original Column | Expected result |
| 08:00 | 8,00 |
| 0,21875 | 5,25 |
The formula I'm trying to find could be something like: If(datatype=Date(hours:minutes) then formula1 else formula2)
But I'm unable to find how to return a data type in a formula...
Any idea please ?
Thanks,
Tof.
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!
5 Replies
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_Super User
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
- PijushRoy
Community Champion
Hi Anonymous
How 0,21875 is calculated as 5.25, please share
