Forum Discussion
Lookupvalue not giving all values
Hi Everybody,
I have a table where I am using the LOOKUPVALUE in a calculated column to take the result from the next row and insert it in the current row, the formula is as follows:
Dep sch = LOOKUPVALUE(Movements[sched_departure], Movements[Unique ID], Movements[Unique ID]+0.1)
This is the set up of the table (called Movements):
Date | ID | Scheduled Departure | Actual Departure | Scheduled Arrival | Actual Arrival | Sequence | Unique ID
The Sequence column contains numbers which list the sequence of events for the relevant ID, the numbers go from 1 to 10 generally. I created the unique ID by joining Date, ID, and Sequence (sequence is divided by 10).
This is the result I am getting:
As you can see some of the info is missing. You can see that some of them are correct.
The reason I am doing this is because I need to work out the difference in minutes between Scheduled departure and the Scheduled arrival, this represents the standing time at location. If you look at the example above where for unique ID 427431310.20 the time is 6:20 pm I need to deduct this from the scheduled arrival time of 5:42pm for the same unique ID. The result would be 38 minutes.
Hope this all makes sense.
Thanks,
Giles
2 Replies
Hi Gile
You might find this useful
The index column is created by Power Query
wynhopkins Thanks for the reply, however there seemed to be an error in the number which only showed when the values were in text. The formula to fix this issue was to do some clever maths in power query:
(Number.Round(([Merged]*0.1)*100)/100)
However, to do what I needed to do I then had to create a second column which added on the 0.1 using the same formula above. This then meant I could bring the next row back, i.e. have row two in row one.
Giles
