Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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!
@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.
Thank you very much for the prompt reponse!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |