question

Supriya20-5011 avatar image
0 Votes"
Supriya20-5011 asked HerbertSeidenberg-6295 answered

More than 24 hour format issue in Power Query

I am picking the csv file from a folder which has a time field of more than 24 hours(format->37:30:55). But when I am using power query(not Power BI) time format is getting changed.

Eg: In my source file I have time as 46:33:10 but in power query it is loading as 1/2/1904 10:33:10 PM.

Now, I want 46:33:10 to be loaded/converted in my power query. I am not using Power BI to use any DAX Query. I have also tried converting it to Duration but it didn't help

power-query-not-supported
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Supriya20-5011

After getting i.e. 46:33:10 in Power Query, do you need to perform any calculation on that value?
If so:
- What kind of calculation?
- In which application (Power Query, Excel...)?

0 Votes 0 ·

@Lz-3068 Yes, Once I get 46:33:10 I need to calculate average and prepare Bar Graph for the fields

0 Votes 0 ·
RonRosenfeld-3452 avatar image
0 Votes"
RonRosenfeld-3452 answered RonRosenfeld-3452 edited

Are you accessing the file directly from Power Query? Or are you first opening it in Excel?
I ask because if I have that value in a csv file, and access the file directly from PQ, I get a text string that looks like a duration and not something that looks like a date/time

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@RonRosenfeld-3452
Firstly,I will place my csv files in a folder and then open it using Data->GetData->FromFile->FromFolder from excel sheet

0 Votes 0 ·

@Supriya20-5011
Firstly,I will place my csv files in a folder and then open it using Data->GetData->FromFile->FromFolder from excel sheet

OK, why not but that non-Excel sheet is going to be opened as a CSV/TEXT file with function Csv.Document. Consequently, as Ron said, you should get a Text value and because time exceeds 24h it cannot be transformed as i.e. DateTime value by a classic Table.TransformTypes

So, to clarify things, could you upload and share (i.e. with OneDrive or any other file sharing service) your CSV (a few rows only) and post the beginning of your query so we have a chance to understand how you can get i.e. 1/2/1904 10:33:10 PM from Text value 46:33:10


0 Votes 0 ·

What Lz wrote. I think we need to see a sample from the actual CSV files from which you are getting these results. Because when I import a CSV file (using that dialog) that contains

46:33:10

it gets imported as text exactly like that; and not as a date.




0 Votes 0 ·
HerbertSeidenberg-6295 avatar image
1 Vote"
HerbertSeidenberg-6295 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.