Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
guyinazo
Helper I
Helper I

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]))

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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]))

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

1 REPLY 1
Fowmy
Super User
Super User

@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]))

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.