Forum Discussion
How to link Table A to Table B - Vlookup?
Hi Guys
Im pretty new to PowerBi so im rely relying on these forums alot.
I have the following DataModel in PowerBI but am struggling to achieve the following:
I have the following table: AccountStructure
It contains the following 2 calculations.
Then i also Have a table caled CostCentreStructure which contains the CCLevel2 calculation
Now this together i created in accountstructure the calculation CCACC which is a unique key which will link to a table i have
called Comments as per above with a calculated column called CommLink
So CommLink will link back to CCACC to provide me with the comments details.
My table linking structure looks as follow,but ofcourse i cant link my Comments Table to my AccountStructure table
So i want to be able to achieve the following:
Any assistance will be greatly appreciated
5 Replies
MalSResolver III
I don't quite follow all the calcs, but I assume the problem is that you can't link AccountStructures[CCACC] to Comments[CommLink] because that is a many to many relationship?
If so, you need another table that contains only unique values of your key field. Then you can create a relationship from both AccountStructures[CCACC] and Comments[CommLink] to this other table.
If you don't have a table of unique values, you can create one. Assuming one of your existing tables contains every possible value of the key field (even if some values occur multiple times in the table) you can:
1. Edit queries
2. Right click on the query that contains every possible value of the key field, and select Reference
3. Right click on the key field column and Remove Other Columns
4. Right click again and select Remove Duplicates
(If there is no single table with every possible value, you can append several queries together first, then follow the steps above)
Thanks alot for the reply.
The problem is not that there is a many to many relationship, i can't even establish the link between CommLink which is a unique id for per year, period.
It just show me this little icon when i drag to make the connection.
Dont know if it might be because if i view the detail of these 2 tables in question i cant actually see the column value of CCACC?
- MalS
Oh, it looks like CCACC is a measure.
Try adding it as a New Column instead (click Edit Queries > Add Column > Custom Column, and enter your formula there). Then see if you can make the connection.
