Need help with merged columns and data types

[MattAdams]

I have a column named resolvedondt and one called resolvedontm from a database. Resolvedondt is a date format and the other is a text (ABC) type. When I do a merge to combine them as separated by a space, I keep losing the date type on the merged column. I know this because I try to do subtraction on another column, subtracting from this format and I keep getting an error that I can't use a - operator with a text and date field. Should I be merging these two columns differently to make it a date field? The merged field shows a date and time field like mm/dd/yyyy hh:mm am/pm.

[Rfranca]

hi, @MattAdams

The resolverondt column is data type ex ... mm / dd / yyyy
The resolvedontm column is text type ex ... (what does this column have in a date type?)

Send a print

And Why do you need to merge these columns?

[MattAdams]

ResolvedonTM has just a time (like 09:00 pm). The database it came from was for incident management. It had 4 columns that I'm trying to calculate duration days using a formula. The four columns are such: openedontm (includes a time) and openedondt (includes a date), and then resolvedontm (is a time) and then resolvedondt (date). I've used merge columns to combine then into a readable format like ex 01/15/2018 09:58pm rather than the two split out so I can calculate how long each record was open. However, the tm columns are showing "ABC" in the column (text) and the dt (date) are showing the little calendar picture (calendar). This picture is the merged columns separated by a space. 

[MarcelBeug · Accepted solution]

You can first convert your text column to type time and then merge the 2 columns (first select the 2 columns) by using the Combine Date and Time functionality.

This is the generated code (with sample data in the Source step):

let
    Source = #table(type table[resolvedondt = date, resolvedontm = text],{{#date(2017,5,10),"3:39 PM"}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"resolvedontm", type time}}),
    #"Merged Date and Time" = Table.CombineColumns(#"Changed Type", {"resolvedondt", "resolvedontm"}, (columns) => List.First(columns) & List.Last(columns), "Merged")
in
    #"Merged Date and Time"

Here you can find Combine Date and Time:

[Rfranca]

hi, @MattAdams

@MarcelBeug is correct in the help, however we can explore a bit more the solution and use the M language functions to ensure that everything will be done in the process steps and also the types of data received were not generating training problems.

See with the solution is easy and can be used in other situations.
Warning for language / country formatting.

The functions M:
Time.ToText () - Returns a text value from a Time value.
Time.FromText () - Returns the Time value from a set of date formats.
Date.FromText () - Returns a Date value from a set of date formats and culture value.
Date.ToText () - Returns a value value from a Date value.
DateTime.FromText () - Returns the DateTime value from a set of date formats and culture value.

let
    Source = Excel.Workbook(File.Contents("C:\Users\adm\Desktop\kundoo6.xlsx"), null, true),
    Planilha1_Sheet = Source{[Item="Planilha1",Kind="Sheet"]}[Data],
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(Planilha1_Sheet, [PromoteAllScalars=true]),
    xDataConvert = Table.AddColumn(#"Cabeçalhos Promovidos", "Custom1", each if ( try Date.ToText([data],"yyyy-mm-dd") otherwise "No") = "No" then Date.ToText([data],"yyyy-mm-dd")  else  [data]),
    xTimeConvert = Table.AddColumn(xDataConvert, "Custom2", each Time.FromText([hora])),
    xDateTimeMerge = Table.AddColumn(xTimeConvert, "Custom3", each Date.ToText([Custom1],"dd/MM/yyyy") & " " &
Time.ToText([Custom2],"hh:mm:ss")),
    xDateTimeConvert = Table.AddColumn(xDateTimeMerge, "Custom4", each DateTime.FromText([Custom3])),
    #"Tipo Alterado" = Table.TransformColumnTypes(xDateTimeConvert,{{"Custom4", type datetime}})
in
    #"Tipo Alterado"

Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

Best Regards,
Rfranca

[MattAdams]

I'll give this a shot and see which works best....thanks!

[MattAdams]

Thanks @MarcelBeug that was perfect!