cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
uabb Frequent Visitor
Frequent Visitor

Time being converted to Text in Power BI from Access database

So I have 2 times, say 07:30 and 09:30. I was trying to subtract time in PBI, and that will get me decimal hours, multiplying it by 24 will get me 1.5 instead of 1:30 (1 hour and 30 minutes).

 

So I went to original access database and there I subtracted time using this: format(time2-time1,"short time").

 

Importing this column into PBI, it shows 1:30 but it is as a text column. Converting it to time column makes all column into error. I need it to be time since I will be adding the numbers up, and text you cannot add up.

 

Would like to know if there is either some way to find time difference within PBI in hours and minutes OR not converting access column into text when importing.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Time being converted to Text in Power BI from Access database

Hi @uabb ,

What about this?

Column = var diff= DATEDIFF('Table'[Time1],'Table'[Time2],SECOND) var h=INT(diff/3600) var m= INT(MOD(diff,3600)/60) return h&"h"&m

Here is the output.

Capture.PNG

If you still need help, please share your data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Time being converted to Text in Power BI from Access database

Hi @uabb ,

I'm a little confused about your scenario.

By my tests in power bi, it seems that Date2-Date1 is like below.

Capture.PNG

If it is convenient, could you share the dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.)

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
uabb Frequent Visitor
Frequent Visitor

Re: Time being converted to Text in Power BI from Access database

Hello

 

The error already exists in your post. When you subtract time, it is giving time in AM and PM.

 

For example, if I subtract 7 AM from 10 AM, i require 2 hours, not 2 AM. The reason being is that I need to subtract 2 hours from another hours time. It will not be possible to subtract 2 AM from say 10 hours (which itself would be 10 AM).

 

The solution is that i subtract time and get decimal hours and then times it by 24 hours. However, for some numbers I will get exact hour, but if the time difference is 1 hours and 30 minutes, it will output the time as 1.50 instead of 1:30.

Community Support Team
Community Support Team

Re: Time being converted to Text in Power BI from Access database

Hi @uabb ,

What about this?

Column = var diff= DATEDIFF('Table'[Time1],'Table'[Time2],SECOND) var h=INT(diff/3600) var m= INT(MOD(diff,3600)/60) return h&"h"&m

Here is the output.

Capture.PNG

If you still need help, please share your data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

PradipMCT Regular Visitor
Regular Visitor

Re: Time being converted to Text in Power BI from Access database

Hi @uabb 

 

You actually don't need to go to Access to do the date/time calculation. In Power Query is much simpler. Once you've imported the respective columns you may change the data type as Date / Time. Now follow the steps given below and match it with the screenshot attached.

 

Step 1: Select the columns

Step 2: Add Column tab --> Time --> Subtract. It will create a new column that will have the difference between both the columns

Step 3: Select the SUBTRACT column

Step 4: Select the Duration --> Select Total Minutes (or anything else as per your need)

Step 5: Total Minutes column has your solution

 

Hope this works. 

 

If this is the solution then please do mark it as a solution. 

 

Pradip Pardeshi

Microsoft Certified Trainer

Pradip's Youtube Channel

 

Time Calculation.png

 

Community Support Team
Community Support Team

Re: Time being converted to Text in Power BI from Access database

Hi @uabb ,

Have you solved  the problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 146 members 1,587 guests
Please welcome our newest community members: