Forum Discussion
Making the Time in to same format
Dear All
I need to make below showing all the transaction time values in to 12 hours format.
I used following code but it gives me errors
try Text.From([Transaction Time])
otherwise
Text.Middle(Text.From([Tme]),0,1) & ":" &
Text.Middle(Text.From([Tme]),1,2) &":" &
Text.Middle(Text.From([Tme]),3,2)
Would really appreciate some help on this. Thank you in advance.
Best Regards,
Dora
Hi,
Thank You
But I am getting errors as follows:
so I try to change the transaction time from following code
if Text.Length([Transaction Time])>6 then [Transaction Time]
else
Text.Middle(Text.From([Transaction Time]),0,2) & ":" &
Text.Middle(Text.From([Transaction Time]),2,2) &":" &
Text.Middle(Text.From([Transaction Time]),4,2)
but It didn`t give the required output.
Hi Dora,
It seems like that there exists some special formats. Like7597, it should be 075907. Right?
For this scenario, my solution will not work. And if there are not many errors. I would suggest you to change the initial Time to the required format manually at Excel file side. I think it will be the easiest method.
Thanks,
Xi Jin.
16 Replies
What is the source format and what is the intended output format? It's not really clear from your post.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
source is in Time Format ,I need the all Time rows without errors and in 12 hours format.
Expected output
Hi Dora,
Check this:
I have imported part of your data as sample:
1. Convert the second Transaction Time column to text type. Then Add a new custom column to format the values under second Time column to 6 digits with expressions like:
if Text.Length([Transaction Time.1])=5 then "0"&Text.From([Transaction Time.1]) else if Text.Length([Transaction Time.1])=4 then "00"&Text.From([Transaction Time.1]) else if Text.Length([Transaction Time.1])=3 then "000"&Text.From([Transaction Time.1]) else if Text.Length([Transaction Time.1])=2 then "0000"&Text.From([Transaction Time.1]) else if Text.Length([Transaction Time.1])=1 then "00000"&Text.From([Transaction Time.1]) else [Transaction Time.1]
2. Add another new custom column and use Text.Middle() function to get the desired time format.
Text.Middle(Text.From([New Time]),0,2) & ":" & Text.Middle(Text.From([New Time]),2,2) &":" & Text.Middle(Text.From([New Time]),4,2)
3. Simply convert this new Result Column to Time type.
The entire Power Query is:
let Source = Excel.Workbook(File.Contents("C:\Users\xxx\Desktop\data.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Amount", type number}, {"Transaction Date", type date}, {"Transaction Date_1", Int64.Type}, {"Transaction Time", type text}, {"Transaction Time_2", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Transaction Time_2", "Transaction Time.1"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Transaction Time.1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "New Time", each if Text.Length([Transaction Time.1])=5 then "0"&Text.From([Transaction Time.1]) else if Text.Length([Transaction Time.1])=4 then "00"&Text.From([Transaction Time.1]) else if Text.Length([Transaction Time.1])=3 then "000"&Text.From([Transaction Time.1]) else if Text.Length([Transaction Time.1])=2 then "0000"&Text.From([Transaction Time.1]) else if Text.Length([Transaction Time.1])=1 then "00000"&Text.From([Transaction Time.1]) else [Transaction Time.1]), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Result", each Text.Middle(Text.From([New Time]),0,2) & ":" & Text.Middle(Text.From([New Time]),2,2) &":" & Text.Middle(Text.From([New Time]),4,2)), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Result", type time}}) in #"Changed Type2"Thanks,
Xi Jin.
