Forum Discussion
Subtraction of 2 time rows
Hello all,
I have a "Tarih.2 (hour:minute:sec)" column in my database and I want to subtract each pair of values in this column.
For example; row2-row1, row4-row3, row6-row5 etc..
After this operation, I need keeps these values into new column.
How can I do this?
Thanks a lot in advance.
Hi Anonymous ,
So, with your table in Power Query, you would select the [Index.1] column, then go to the Home tab and select Merge.
In the dialog, you would use the same table for both the left and right members of the merge i.e. you are joining/merging the table on itself:
When the merge completes, you will have a column full of nested tables. Expand these tables by hitting this button:
And select just your [Tarih.2] column from the list:
This should give you a table like this where the previous [Tarih.2] value is on the same row as the current value:
This now makes it really easy to do comparisons of the two values on each row.
Pete
4 Replies
- BA_Pete
Super User
Hi Anonymous ,
It looks like you're already halfway through the solution I would propose in that you've got two index columns offset by 1.
I would merge your table on itself as table[Index.1] = table[Index] and expand the [Tarih.2] column after the merge.
You then have the two times inline on the same row to make the subtraction on using Duration functions or whatever method you choose.
Pete
Hi BA_Pete ,
Actually, I couldn't fully understand. Can you explain as more detail?
Thanks a lot,
Eren
- BA_Pete
Hi Anonymous ,
So, with your table in Power Query, you would select the [Index.1] column, then go to the Home tab and select Merge.
In the dialog, you would use the same table for both the left and right members of the merge i.e. you are joining/merging the table on itself:
When the merge completes, you will have a column full of nested tables. Expand these tables by hitting this button:
And select just your [Tarih.2] column from the list:
This should give you a table like this where the previous [Tarih.2] value is on the same row as the current value:
This now makes it really easy to do comparisons of the two values on each row.
Pete
Syndicate_AdminAdministrator
Hi @ernu ,
So, with your table in Power Query, you would select the [Index.1] column, then go to the Home tab and select Merge.
In the dialog, you would use the same table for both the left and right members of the merge i.e. you are joining/merging the table on itself:
When the merge completes, you will have a column full of nested tables. Expand these tables by hitting this button:
And select just your [Tarih.2] column from the list:
This should give you a table like this where the previous [Tarih.2] value is on the same row as the current value:
This now makes it really easy to do comparisons of the two values on each row.
Pete