Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello guys,
I'm new to power BI and I really need help in getting the time difference from a single column,
I have the Date and and Index. I needed an output like this. Can someone help me
Time Difference | |||
Date | Index | ||
Wed, 01 Mar 2023 07:00:53 | 0 | ||
Wed, 01 Mar 2023 07:01:12 | 1 | 0:00:19 | |
Wed, 01 Mar 2023 07:01:23 | 2 | 0:00:11 | |
Wed, 01 Mar 2023 07:01:30 | 3 | 0:00:07 | |
Wed, 01 Mar 2023 07:07:30 | 4 | 0:06:00 | |
Wed, 01 Mar 2023 07:09:30 | 5 | 0:02:00 |
Solved! Go to Solution.
In Power Query (Transform Data), you can
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk9N0VEwMFTwTSxSMDIwMlYwMLcyMLAyNVaK1cEha2hlaIRP1givXmMD3LLmeGUtwbKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
#"Add Shifted Date Column" =
Table.FromColumns(
Table.ToColumns(#"Changed Type") &
{{null} & List.RemoveLastN(#"Changed Type"[Date],1)},
type table[Date=datetime, Shifted Date=nullable datetime]),
#"Add Time Difference Column" = Table.AddColumn(#"Add Shifted Date Column", "Time Difference",
each [Date] - [Shifted Date], type duration),
#"Remove Shifted Date Column" = Table.RemoveColumns(#"Add Time Difference Column",{"Shifted Date"})
in
#"Remove Shifted Date Column"
You could also do this with DAX, using a similar algorithm.
Again, the below assumes the DATE column is sorted ascending.
You select to add a column, with this DAX formula:
Time Diff = var SHIFT = OFFSET(-1,ALL('Table'[Date]))
return if(SHIFT=0,BLANK(),'Table'[Date] - SHIFT)
In Power Query (Transform Data), you can
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk9N0VEwMFTwTSxSMDIwMlYwMLcyMLAyNVaK1cEha2hlaIRP1givXmMD3LLmeGUtwbKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
#"Add Shifted Date Column" =
Table.FromColumns(
Table.ToColumns(#"Changed Type") &
{{null} & List.RemoveLastN(#"Changed Type"[Date],1)},
type table[Date=datetime, Shifted Date=nullable datetime]),
#"Add Time Difference Column" = Table.AddColumn(#"Add Shifted Date Column", "Time Difference",
each [Date] - [Shifted Date], type duration),
#"Remove Shifted Date Column" = Table.RemoveColumns(#"Add Time Difference Column",{"Shifted Date"})
in
#"Remove Shifted Date Column"
You could also do this with DAX, using a similar algorithm.
Again, the below assumes the DATE column is sorted ascending.
You select to add a column, with this DAX formula:
Time Diff = var SHIFT = OFFSET(-1,ALL('Table'[Date]))
return if(SHIFT=0,BLANK(),'Table'[Date] - SHIFT)
From what I understand, you want the time difference from the previous row.
It's not going to be pretty but it should work:
1) Looks like you have already done a SORT BY on Date and added the Index column. Good keep those, you are gong to need them.
2) DUPLICATE this query. Add a Custom Column named [New Index] and make it equal [index] - 1.
3) MERGE the two queries. Join on [Index] from the original table and [New Index] of the new table.
4) Expand the new table and grab only the Date.
You should now have one date/time and the previous date/time on one row.
Do the match between the two.
FYI: If this is SQL, you should investigate WINDOW functions, particularly LAG and LEAD which allow you to find the row X number of rows behind (LAG) or x number of rows ahead (LEAD) of the current rwo. Plus anytime you can do something in SQL instead of Power Query, do it.
Proud to be a Super User! | |