Forum Discussion
Lookupvalue will not return time value
Hi,
I've been working on this for six hours, so I thought I would try the community for an answer! I have Googled/lurked the forums to no avail.
I have two tables of data: I'm trying to populate a column in one with data from the other using LOOKUPVALUE.
Table One has a list of specific events:
| Date | Truck | JoinKey | Event Type | Time of Event | Carrying |
| 01/01/2016 | Red | Red42370 | Time Departed | 12:01:00 PM | Apples |
| 01/01/2016 | Blue | Blue42370 | Time Departed | 1:05:00 PM | Oranges |
| 01/01/2016 | Yellow | Yellow42370 | Time Departed | 7:33:00 AM | Bananas |
| 01/01/2016 | Red | Red42370 | Time Arrived | 2:53:00 AM | Bananas |
| 01/01/2016 | Blue | Blue42370 | Time Arrived | 4:32:00 PM | Bananas |
| 01/01/2016 | Yellow | Yellow42370 | Time Arrived | 9:32:00 AM | Oranges |
| 02/01/2016 | Yellow | Yellow42371 | Time Departed | 5:24:00 AM | Apples |
| 02/01/2016 | Red | Red42371 | Time Departed | 11:24:00 PM | Apples |
| 02/01/2016 | Blue | Blue42371 | Time Departed | 8:11:00 PM | Oranges |
| 02/01/2016 | Yellow | Yellow42371 | Time Arrived | 12:00:00 AM | Oranges |
| 02/01/2016 | Red | Red42371 | Time Arrived | 4:22:00 AM | Bananas |
| 02/01/2016 | Blue | Blue42371 | Time Arrived | 3:25:00 PM | Oranges |
Here is Table 2, the one I'm trying to use LOOKUPVALUE with:
| Date | Truck | JoinKey | Time Departed | Time Arrived |
| 01/01/2016 | Red | Red42370 | ||
| 01/01/2016 | Blue | Blue42370 | ||
| 01/01/2016 | Yellow | Yellow42370 | ||
| 02/01/2016 | Red | Red42371 | ||
| 02/01/2016 | Blue | Blue42371 | ||
| 02/01/2016 | Yellow | Yellow42371 |
I am using LOOKUPVALUE with a unique column, "JoinKey" to populate with the values from Table 1. There is only one instance of departed and arrival times for each day and "truck".
Time Departed = LOOKUPVALUE(Table1[Time of Event],Table1[JoinKey],Table2[JoinKey],Table1[Event Type],"Time Departed")
However, I keep getting the error, "A table of multiple values was supplied where a single value was expected."
The "JoinKey" column for Table2 has unique values and I have built a many:1 relationship from Table 1 to it. To test it, I have tried subsituting the "Time of Event" value in the formula with other columns, e.g. "Carrying", and it works fine. The LOOKUPVALUE returns the value from every other column except "Time of Event".
Is LOOKUPVALUE not compatible with time values? I've tried formatting it to text and it still won't work. If I'm not making any sense please let me know... I think I've been staring at this for too long.
Any assistance you could provide would be greatly appreciated!
Thank you,
Kristen
Hi Kristen,
You can use Query editor as well to solve this type of problem.
Just few mouse clicks and you are done. Follow the screenshots for exact solution.
Merge Table 2 and Table 1 as shown in the ScreenshotExpand the new column and select Event Type & Time of Event CheckboxesChange the data Type of the last column to Time and Rename both the columnsTime of the Event Column with new Data TypePivot the Column. Select the options shown in the screenshotAll done. TA....DA........
4 Replies
Anonymous You need to have unique combinations of [JoinKey] and [Event Type]
To test this create a COLUMN In Table 1 Create
Column = Table1[JoinKey]&" - "&Table1[Event Type]
Then create this MEASURE
Transactions = COUNTROWS(Table1)
Then create a Visualization Table with the Column and Transactions.
If you see anything other than 1 for each column - that will be what's causing the problem!
Basically the function will have more than 1 possible answer that meets your criteria.
Hope this helps and makes sense! :smileyhappy:
You can use the Visual Level Filters to => show Transactions more than 1 so you can identify the problem faster.
EDIT: Just to clarify - my post was merely answering your question about the error message you get with the LOOKUPVALUE.
I would personally also use with the Query Editor to shape your original dataset better.
Hi Kristen,
You can use Query editor as well to solve this type of problem.
Just few mouse clicks and you are done. Follow the screenshots for exact solution.
Merge Table 2 and Table 1 as shown in the ScreenshotExpand the new column and select Event Type & Time of Event CheckboxesChange the data Type of the last column to Time and Rename both the columnsTime of the Event Column with new Data TypePivot the Column. Select the options shown in the screenshotAll done. TA....DA........
