Hi. I am very new to Power Query and a bit of an amateur on excel as I still google lots of formulas that I use.
I have been running a weekly report on excel, that when setting up, involves changing numbers into a time format eg. 850 would change to 08:50:00 and 1155 would change to 11:55:00. To get this IN Excel I use the formula =TIME(LEFT(C4,LEN(C4)-2),RIGHT(C4,2),)
When I try this in PQ it does not like TIME.
Would anyone be able to help me with the formula in PQ when I am creating a custom column?
Thanks
Chris
I should have said that C4 is just the location that I happened to copy this from.
Hi Chris,
Welcome to our forum! I already answered this question on YouTube where you posted it originally:
First split the column by position by 2 characters and once, as far right as possible. Make sure the data type for the two new columns is a whole number. Then add a custom column using the #time function like so: #time([Time.1],[Time.2],0)
I hope that helps.
Mynda
Mynda,
Would you be able to provide a link to the detailed answer or to the original post where I can find your answer? I have the same issue, where I need to convert a time entered as "0700" to "7:00:00 AM") and am unable to find resources through my searches.
Trying to follow your answer above, I encounter errors. I'm clearly missing something.
Thanks in advance!
Ray
Hi Ray,
The above is the detailed answer 🙂
If you're experiencing errors I'd guess it's due to the data type, but without knowing the error or seeing your file it's a complete guess.
If that doesn't point you in the right direction, please come back with a sample Excel file showing what you've tried so we can help you further.
Mynda
