Substract columns with different data type and create a new column

[Pranay08]

These are two columns of two different tables, with different data types and i need to substract first column with the second and find out the time delay . While subtracting i need to consider date of the first column only . Is there any way to do this 

[v-yaningy-msft · Accepted solution]

Hi, Pranay08

May I ask if this is the expected output you are looking for? Based on your description, I'm not sure which effect you want, the following diagram implements both effects.

Effect 1:

Effect 2:

Advanced Editor:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc9LCsAgDEXRrYhjQV+M/WTmAgqdi/vfRikUEgU7vOQMXlrzQMQeKVF2p6BISq5ePngTPQwMkKLO1gxpgLSEWVgda0yM7T5e7tuElB1/b2B4Ywm/y/26rDExssxE7w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Time" = _t, #"Flight Time" = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Time", type datetime}, {"Flight Time", type time}}),

    #"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Date Time", type text}}, "en-US"), "Date Time", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Date Time.1", "Date Time.2"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Date Time.1", type date}, {"Date Time.2", type time}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Flight Time]-[Date Time.2]),

    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each DateTime.From([Date Time.1]) - Duration.From([Custom]))

in

    #"Added Custom1"

If this does not work, could you please share some sample data without sensitive information and expected output.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum