I have two tables A, B connected by bridge relationship through the bridge table with unique ID.
Table A contains column ID: unique ID values, and column Type: repeated values and column Date: repeated values.
Table B contains column ID: repeated values.
I want to LOOKUPVALUE of Type and Date from A to B but I only made it with Type, and when it comes to Date, it warns:
A table of multiple values was supplied where a single value was expected
I am confused and would very much be appreciated if anyone gives me some insights, or other solution.
I tried with your Table A and Table B with NO relationship between them. It seems that the LOOKUPVALUE function works well.
I’ve uploaded my .pbix file here for reference. Could you please take a look at it to see if there is any difference between us?
LOOKUPVALUE ( 'Table A'[Type], 'Table A'[ID], 'Table B'[ID] )
LOOKUPVALUE ( 'Table A'[Date], 'Table A'[ID], 'Table B'[ID] )
PowerQuery is handy, quick and easy solution for such scenarios.
As shown in the screen shot, Go to the Query mode and In the Home Tab, Go to the Last section Combine. Click Merge Queries as New in here and select the Right Outer Join.
Expand the column and Delete the newly created column. This will give you the results expected.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.