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 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.

 

 

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:

     

6 Replies

  • Rfranca's avatar
    Rfranca
    Icon for Resolver IV rankResolver IV
    8 years ago

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

      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's avatar
        MarcelBeug
        Icon for Community Champion rankCommunity Champion
        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: