Question: How to preserve mixed datatypes in a column

[Anonymous]

I have a spreadsheet with a column showing measurements, but the measurements are a combination of numeric levels and durations, e.g.:

Row 1: 5.3

Row 2: 7

Row 3: 00:03:01

When PowerBI imports the data it converts the duration values into datetime values and I end up with:

Row 1: 5.3

Row 2: 7

Row 3: 12/31/1899 12:03:01 AM

I've tried reformatting the column datatype, but there doesn't seem to be a way to do that with mixed datatypes. The obvious solution of converting to Text doesn't work because it converts after the initial conversion, so the datetime gets converted, rather than the duration.

Hoping someone has a suggestion!

[VasTg]

@Anonymous 

When i used the data you provided, Power BI automatically inserted a step "Changed Type" after source. But in my case it is converted to Text. 

Delete that step and manually chage the datatype to Text.

If it helps ,mark it as a solution

Kudos are nice too

[Anonymous]

Thanks for the suggestion, unfortunately that didn't work with my data:

Raw Excel data:

Test Result
20.2977
20.5479
300
300
1:10:00
1:10:00
0:13:32
222.222
266.667

Following the steps you suggested I still had datetimes, and my numeric values got distorted as well. Sorry, having problems with the images. The second image was the initial import, the third image was after I removed the "Changed Type" and the first image is the final result.

[v-kelly-msft]

Hi @Anonymous，

A strange thing that since you have removed the step of "changed type",why it still remains in your steps?

If removing the step cant work,you can try to modify the step in advanced editor:

Go to "edit queries">"advanced editor:

Put the M code in the editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtUzVorViVYyB5MKBgZWBsZWBoZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}})
in
    #"Changed Type"

 Try this way to see if it works.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thanks for the insight into how to change datatypes in the code. Unfortunately, this is still post import, so it has the same result as before. Dates get converted to text, rather than durations.

Looks like I'll have to change the datatypes in the source Excel spreadsheet. 

[VasTg · Accepted solution]

@v-kelly-msft 

When I pasted the sample data in excel, by default the duration is coverted into am/pm. When I select the column and change the datatype to text, it is modified into some fixed values and doesn't stay as duration. 

So the problem exist in excel itself as @Anonymous mentioned the source as excel.