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
Anonymous
Not applicable

subtracting time from date/time power query

Hello

 

I have a table with date/time column and time column. I want to subtract the time column from the date/time column in Power Query, but I receive this message 

 

Expression.Error: We cannot apply operator - to types DateTime and Time.
Details:
Operator=-
Left=07/04/2019 14:00:00
Right=00:00:00

 

i used a simple formula when using the Adding aCustom Column  

 

=[date/time] - [time]

 

I know it's possible with DAX, but i need to do it within Power Query

 

Thanking you in advance

2 ACCEPTED SOLUTIONS

Hi @Anonymous ,

If you want to achieve your desired output with Power Query, you could refer to my code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ11DVVMDCyMjS1MjBQ0lEyMATSIGasDlyBIYoCA5iCWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date/Time" = _t, Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"Time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.Time([#"Date/Time"])-[Time]),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-US"), "Custom", Splitter.SplitTextByPositions({0, 1}, false), {"Custom.1", "Custom.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Position",{"Custom.1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Columns", {{"Date/Time", type text}}, "en-US"), "Date/Time", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Date/Time.1", "Date/Time.2"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Split Column by Delimiter", "Date/Time.1", "Date/Time.1 - Copy"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Duplicated Column", "Date/Time.1 - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date/Time.1 - Copy.1", "Date/Time.1 - Copy.2", "Date/Time.1 - Copy.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Date/Time.1", type datetime}, {"Date/Time.2", type text}, {"Custom.2", type time}, {"Date/Time.1 - Copy.1", type date}, {"Date/Time.1 - Copy.2", type time}, {"Date/Time.1 - Copy.3", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Date/Time.2", "Date/Time.1 - Copy.2", "Date/Time.1 - Copy.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Date/Time.1 - Copy.1", type text}, {"Custom.2", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom", each [#"Date/Time.1 - Copy.1"]&" "&[Custom.2]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type datetime}})
in
    #"Changed Type3"

Here is the output.

Capture.PNG

In addition, @ImkeF may have other simpler solution for your requirement.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

The key lies in not transforming the Time-column to Time, but to duration instead in the first "Changed Type"-step.

Then you can simply subtract it like so:

 

let 
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ11DVVMDCyMjS1MjBQ0lEyMATSIGasDlyBIYoCY5iCWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date/Time" = _t, Time = _t]), 
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"Time", type duration}}), 
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [#"Date/Time"]-[Time])
in
    #"Added Custom"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
Gordonlilj
Solution Sage
Solution Sage

Hi,

 

You could use the code below in a custom column and it should work

 

DateTime.Time([Table])

Just switch the table to your own

 

Anonymous
Not applicable

Thanks for your reply.

 

I think there was a missunderstanding.

 

I have two columns one with Date/Time and another with Time only. Time column is populated by 00:00:00  or 01:00:00 and I want to subtract these from the Date/Time column

 

Date/Time                                    Time                            UTC Time

01/05/2019 02:15:00                01:00:00                       01/05/2019 01:15:00

01/01/2019 02:15:00                00:00:00                       01/01/2019 02:15:00

 

 

Kind regards

 

Hi @Anonymous ,

If you want to achieve your desired output with Power Query, you could refer to my code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ11DVVMDCyMjS1MjBQ0lEyMATSIGasDlyBIYoCA5iCWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date/Time" = _t, Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"Time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.Time([#"Date/Time"])-[Time]),
    #"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-US"), "Custom", Splitter.SplitTextByPositions({0, 1}, false), {"Custom.1", "Custom.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Position",{"Custom.1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Removed Columns", {{"Date/Time", type text}}, "en-US"), "Date/Time", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Date/Time.1", "Date/Time.2"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Split Column by Delimiter", "Date/Time.1", "Date/Time.1 - Copy"),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Duplicated Column", "Date/Time.1 - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Date/Time.1 - Copy.1", "Date/Time.1 - Copy.2", "Date/Time.1 - Copy.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Date/Time.1", type datetime}, {"Date/Time.2", type text}, {"Custom.2", type time}, {"Date/Time.1 - Copy.1", type date}, {"Date/Time.1 - Copy.2", type time}, {"Date/Time.1 - Copy.3", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Date/Time.2", "Date/Time.1 - Copy.2", "Date/Time.1 - Copy.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Date/Time.1 - Copy.1", type text}, {"Custom.2", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom", each [#"Date/Time.1 - Copy.1"]&" "&[Custom.2]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type datetime}})
in
    #"Changed Type3"

Here is the output.

Capture.PNG

In addition, @ImkeF may have other simpler solution for your requirement.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

The key lies in not transforming the Time-column to Time, but to duration instead in the first "Changed Type"-step.

Then you can simply subtract it like so:

 

let 
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ11DVVMDCyMjS1MjBQ0lEyMATSIGasDlyBIYoCY5iCWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date/Time" = _t, Time = _t]), 
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time", type datetime}, {"Time", type duration}}), 
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [#"Date/Time"]-[Time])
in
    #"Added Custom"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.