Forum Discussion

guyinazo's avatar
guyinazo
Icon for Helper I rankHelper I
4 years ago
Solved

Adding a date column in power query and formatting date based on another value

Hello,  I hope this is simple and I am learning the power of M and Power Query.  We are importing an excel file with dates and times.  There is a Start Time and End Time column and a separate Date-only.  If the time value in this column has a plus sign (+), then that indicates the time is one day following the Date column.  My task is to combine these columns and have a start_date, and end_date column.

 

This is my formula, but I get an error about The Date value must contain the Date component.  Thanks for the help

= Table.AddColumn(#"Promoted Headers", "Start Date Time", each if Text.Contains( [Start Time],"+") then Date.AddDays([Date],1) & " " & Time.Hour([Start Time]) & ":" & Time.Minute([Start Time]) else [Date] & " " & Time.Hour([Start Time]) & ":" & Time.Minute([Start Time]))

 

Need Help
  • Fowmy's avatar
    Fowmy
    4 years ago

    guyinazo 

    You need to extract the Date from the Date/Time column. Check if the columns are in the right format

    = Table.AddColumn(#"Promoted Headers", "Start Date Time", each if Text.Contains( [Start Time],"+") then Date.AddDays(Date.From([Date]),1) & " " & Time.Hour([Start Time]) & ":" & Time.Minute([Start Time]) else [Date] & " " & Time.Hour([Start Time]) & ":" & Time.Minute([Start Time]))

     

1 Reply

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    guyinazo 

    You need to extract the Date from the Date/Time column. Check if the columns are in the right format

    = Table.AddColumn(#"Promoted Headers", "Start Date Time", each if Text.Contains( [Start Time],"+") then Date.AddDays(Date.From([Date]),1) & " " & Time.Hour([Start Time]) & ":" & Time.Minute([Start Time]) else [Date] & " " & Time.Hour([Start Time]) & ":" & Time.Minute([Start Time]))