Forum Discussion
Output Data from three different tables
Hi,
I've made the relationships between those tables with those lookup columns.
Edit Queries - Merge Queries - Merge Queries ->
I then merged matching columns from my tables.
I have used 'Left Outer(all from first, matching from second)' and 'Full Outer (all rows from both)' and Inner Join..
But I keep getting the below errors:
" A table of multiple values was supplied where a single value was expected."
I've sent the PBIx file to your email.
Please Help
Anonymous
I can only view two tables values from your .pbix due to permission. I noticed that you join same table twice in Mnemonic table. If this is the "POPOUT" table, you can expand table to get expected columns after the "Merged Queries" step. Then merge all three CATALOGUE, AREA, HOSE table and expand columns for your LOOKUP.
As you said, you keep getting " A table of multiple values was supplied where a single value was expected." error. This error means the value you are going to mapping has multiple values. Since you already build the relationship between those tables, when doing "Merge Query", you should select the connecting columns for built relationship, which means at list one column is with unique values ("One" side).
But, it still may not work. For exmaple, if POPOUT then "ONE" side in your relationship, and you have One-To-Many to all other three tables. So all three tables join into POPOUT table, appear as a Table object on row level. If you want to mapping values from "Many" side tables each other, you may never get the an unique loopup value for a single entry.
The workaround I suggest is to include the column from the third table into your first table. Then it still became a "Two Table" scenario for your LOOKUPVALUE() function.
Regards,