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

The 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.

Reply
FrossiREAL
Regular Visitor

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

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?

 

Sem título.png

2 REPLIES 2
Smauro
Solution Sage
Solution Sage

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




Spyros Mavroforos

Data Consultant


Find me on LinkedIn or drop me an email

eSpyros

Jimmy801
Community Champion
Community Champion

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).

Jimmy801_0-1602070938652.png

 

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

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors