Forum Discussion

MattAdams's avatar
MattAdams
Icon for Helper I rankHelper I
8 years ago
Solved

Need help with merged columns and data types

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 s...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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's avatar
Rfranca
Icon for Resolver IV rankResolver IV
8 years ago

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's avatar
MattAdams
Icon for Helper I rankHelper I
8 years ago

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

  • MattAdams's avatar
    MattAdams
    Icon for Helper I rankHelper I
    8 years ago

    Thanks MarcelBeug that was perfect!