Question: How to preserve mixed datatypes in a column

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!

Need Help

VasTg
6 years ago
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.

5 Replies

VasTg
Memorable Member
6 years ago
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
  Not applicable
  6 years ago
  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
    Community Support
    6 years ago
    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!