Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to Solution.
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.
@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
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.
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.
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
138 | |
70 | |
65 | |
52 | |
52 |
User | Count |
---|---|
210 | |
92 | |
64 | |
59 | |
56 |