Lookupvalue will not return time value

[Anonymous]

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

[BhaveshPatel · Accepted solution]

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........

[Anonymous]

Thank you so much for this!  I had tried merging queries but pivoting was completely new to me. Grateful to have learned something new.

Thank you!

[Sean]

@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!

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.

[Anonymous]

Thank you very much for the prompt reponse!