Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create a Timestamp from multiple columns in power query

Hello,

 

I'm relatively new to Power BI. I am pulling in data from an API source, which separates out date and time into multiple columns. The data looks like this...

YearMonthDayHour
201911250

 

I need to create a new column that combines these into a single timestamp.   

 

11/25/2019 12:00:00 AM

 

In M code, how can I create a custom column that combines these columns into a timestamp?

 

Thank you for your help!

Need Help
  • ImkeF's avatar
    ImkeF
    6 years ago

    you can just concatenate the time-value with the date-value like so:

     

    DateTime.From(#date([Year],[Month],[Day]) & #time([Hour],0,0))

4 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi Anonymous ,
    This will get you part of the way. Go to Add Column tab, Custom Column. Not sure how the Hour is converted, though.
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This does work for the date piece. I tried using Time.From([Hour]), but I'm getting an error on everything less than 10. Is it a formatting issue?

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        6 years ago

        Hi Anonymous ,
        I have tried changing the data type, etc.  Time to call for the Magician of M. ImkeF 
        Nathaniel