CODEWITHSUNDEEP
powerbi
Omar AlSaghier
Omar AlSaghier

Reputation: 340

Power BI: shifting a column one row above

I have a case that I want find the duration for each "state_id", by subtracting its change_time from the time just below it.

So I want to generate a new column that duplicates the column "change_time" and shifting all values one row up, so the change_time #2(in red) would come next to change_time #3(in red).

Is there a way to shift that column one row up?

enter image description here

Upvotes: 1

Views: 4721

Answers (1)

Omar AlSaghier
Omar AlSaghier

Reputation: 340

After a lot of thinking and researching. I have solved it with the following steps:

  • Sort you columns of interest from A-Z (in my case sort: ticket_id, then history_type_id, then stat_id)
  • make a first new index column starting from 0 using "Index column from 0".
  • make a second new index column starting from 1 using "Index column from 1".
  • merge your query with itself using "merge query" based in the two new indices.
  • expand the merged query for the date column only. Now you will have a new date column, duplicated from the old one with a shift of one row.

enter image description here

2) enter image description here

Upvotes: 1

Related Questions