Convert decimal number to time

[uabb]

So I am importing data from Access and in the power query editor I have tried transform to time, but i get "ERROR".

I have 2 times

One column has time in format such as 71500 or 151500 and other column has 715 or 1214. In the first case, the time has seconds, which I don't need as all are 00. It does not show 0 in the beginning, so it goes 8 AM being 800 and 3PM being 1500 etc. In the second case, seconds do not exist, and again 0 does not exist. 

In addition, should I be fixing/labeling data in Access or simply do it in Access? How does it work on the load? I also skip Excel and work directly with the file in PBI.

I frequently run out on 32 bit BI, and i can't use 64bit BI since our server with data is 32bit. 

[SteveCampbell]

In Power Query, you can add a custom column (Add Column tab > Custom Column):

Text.PadStart(Number.ToText([Column1]), 6, "0")

Replace Column1 with your column.

Then, select this column, go to to transform and change the data type to time.

Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on  
Read my blogs on  

Remember to spread knowledge in the community when you can!