Time base as date 31/12/1899 insted of simple hour format

[FrossiREAL]

Hey guys, some weird import settings over here.

All I want is a simple HH:MM colum, but POwerBi import as:

DD:MM:YYYY HH:MM

It wouldn't be a problem, just trim the date in "transform" into the PowerQuery. But when the time is longer than 24 hours, it has a date shift.

Like this:

31/12/1899 14:00 equals to 14:00

01/01/1990 14:00 equals to 38:00

To solve this ive created a new colum that takes the DATE - 31/12/1899 that equals number of days, then multiplied *24 hours then made a sum to original vale.

This is not elegant at all.

Is there anyway more simple to do it?

[Smauro]

Hi @FrossiREAL ,

You could use something like:

Table.ReplaceValue(#"Previous Step",#datetime(1899,12,31,0,0,0),null,(x, y, z) as duration => Duration.From(DateTime.From(x) - y ),{"Saldo final na data +", "Saldo final na data -", "HORAS A VENCER 20/10 +"})

adding all the column names you need to make this replacement in the list.

Cheers

[Jimmy801]

Hello @FrossiREAL 

the thing here is that Power Query is not importing the real cell value (a number) or the visualized (39:14) but the date-format, meaning 39h and 14 minutes after 31.12.1899 00:00 (start of date in Excel).

This logic is not applicable in Power Query and duration in Power Query are not shown as [h]:mm:ss but as dd.hh.mm.ss. When you really need the [h]:mm:ss-format in power query then you don't have any other chance then calculate the duration (subtracting datetime 31.12.1899 00:00) and create a text string with Text.From(Duration.Hours(Yourduration)) & ":" & etc. But what is the downside for you to use the duration-format in power query - meaning dd.hh.mm.ss?

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy