Making the Time in to same format

[Dora]

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

[Dora · Accepted solution]

Hi,

Thank You

But I am getting errors as follows:

[Dora · Accepted solution]

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.

[Greg_Deckler]

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

[Dora]

source is in Time Format ,I need the all Time rows without errors and in 12 hours format.

Expected output 

[v-xjiin-msft]

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.

[Dora · Accepted solution]

Hi,

Thank You

But I am getting errors as follows:

[v-xjiin-msft · Accepted solution]

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.

[Dora]

Hi @v-xjiin-msft

Yes, that is the issue.  Thank you

[v-xjiin-msft]

Hi @Dora,

Did you resolve your issue? If so, please kindly mark the corresponding reply. It will help others with relevant issues find the answer more easily.

If not, please feel free to tell me.

Thanks,
Xi Jin.

[Dora]

Still Searching for a way to do it at once.If Cannot I am hoping to format excel as you said.

Thank You,

Best Regards,

Dora

[Dora · Accepted solution]

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.

[Ashish_Mathur]

Hi,

You are getting the error there because there is no space between the time stamp and AM.  Insert a space in the Excel file and all will work well.

[Dora]

No ,There is space between time stamp and AM.I need to make the Transaction Time into AM,PM Format.

[Ashish_Mathur]

Hi,

Share the link from where i can download your Excel file.

[Dora]

https://drive.google.com/file/d/1CcNMVXWXwxRuVdiVrig901BEqR-v2C9a/view?usp=sharing

[Ashish_Mathur]

Hi,

Sorry, i do not think i can help.  Someone else will help you.

[Dora]

ok ,thank you

[Dora]

Do any one know about this.