Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
I have two columns (StartTime and EndTime) where I want to create a new custom column where I subtract only first value of StartTime from each value in EndTime. Data time of both columns is Time. Suggestions appreciated.
Solved! Go to Solution.
Hi @davidz106 ,
You can get it in Power Query Editor, please find the details in the attachment.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYzJEQAhCARz4e0DEK9JxTL/NBZhfXZP1+xNCm5UqEOUTtlUIeY8oBxs4GR7bLef4Brcb1hovX6AV3DLvwmdzsLgGWJB5AqB5oNPar8ZaWJzo7BO53w=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartTime = _t, EndTime = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartTime", type time}, {"EndTime", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [EndTime]-#"Changed Type"[StartTime]{0}),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type duration}})
in
#"Changed Type1"
Best Regards
Hi @davidz106 ,
You can get it in Power Query Editor, please find the details in the attachment.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYzJEQAhCARz4e0DEK9JxTL/NBZhfXZP1+xNCm5UqEOUTtlUIeY8oBxs4GR7bLef4Brcb1hovX6AV3DLvwmdzsLgGWJB5AqB5oNPar8ZaWJzo7BO53w=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartTime = _t, EndTime = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"StartTime", type time}, {"EndTime", type time}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [EndTime]-#"Changed Type"[StartTime]{0}),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type duration}})
in
#"Changed Type1"
Best Regards
@davidz106 , Min and Max based on what
overall
a new column
datediff(Min(Table[StartTime]), Max(Table[EndTime]) , second)
or based on id
datediff(MinX(filter(Table, [ID] = earlier([ID]) ), Table[StartTime]), Max(filter(Table, [ID] = earlier([ID]) ), Table[EndTime]) , second)
Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.