Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Hiveman
Regular Visitor

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!

1 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 @Hiveman mentioned the source as excel. 

Connect on LinkedIn

View solution in original post

5 REPLIES 5
VasTg
Memorable Member
Memorable Member

@Hiveman 

 

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. 

image.png

Delete that step and manually chage the datatype to Text.

 

If it helps ,mark it as a solution

Kudos are nice too

 

Connect on LinkedIn

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.

changed.JPGinitial.JPG

 

Hi @Hiveman

 

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:

Annotation 2020-02-17 145914.png

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!

 

 

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. 

@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 @Hiveman mentioned the source as excel. 

Connect on LinkedIn

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors