Identify format type in if formula

[Anonymous]

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.

[Anonymous]

Thanks @v-jingzhan-msft ,

Exactly what I needed !... 👍

[v-jingzhan-msft · Accepted solution]

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!

[PijushRoy]

Hi @Anonymous 

How 0,21875 is calculated as 5.25, please share

[Anonymous]

Thanks for you answer vicky_.

I'll have a look at the article to see if I can find a solution.

[vicky_]

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