Forum Discussion

AColeman's avatar
AColeman
Frequent Visitor
9 years ago

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:...
Need Help
Tips and Tricks
MalS's avatar
MalS
Icon for Resolver III rankResolver III
9 years ago

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)

AColeman's avatar
AColeman
Frequent Visitor
9 years ago

MalS

 

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's avatar
    MalS
    Icon for Resolver III rankResolver III
    9 years ago

    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.

     

    • AColeman's avatar
      AColeman
      Frequent Visitor
      9 years ago

      MalS, nope didnt work and i think its a bit more complicated, as this solution i imported from PowerPivot into PowerBI.

       

      CCACC = [AccCom]&[CCLevel2]

       

      Now AccCom is from Table AccountStructures

       

      AccCom = IF (AND(Actuals[Actuals]=0,Budgets[Budgets]=0),BLANK(),IF ([BrowseDepth]=2, CALCULATE(min([Acc_Level11]),filter('Accounts',[AccRows]>0)),
      IF ([BrowseDepth]=3,CALCULATE(min([Acc_Level12]),filter('Accounts',[AccRows]>0))))) 

       

      Now CCLevel2 is from Table CostCentreStructure

       

      CCLevel2 = IF (AND(Actuals[Actuals]=0,Budgets[Budgets]=0),BLANK(),CALCULATE(min([CostCentre_Level10]),filter('CostCentres',[ccRows]>0)))

       

      But when i copy CCACC to a new column it only returns AccCom values and nothing for CCLevel2

       

      even if i try changing it to CONCATENATE AccountStructures[AccCom],CostCentreStructures[CCLevel2]

       

      But when i drag CCLevel2 into a grid visualization i see the data.....

      • MalS's avatar
        MalS
        Icon for Resolver III rankResolver III
        9 years ago

        Hmm - not really sure why you would see values from AccCom but not CCLevel2. It's a bit diffcult to figure out without the .pbix file to play around with. 

         

        I would probably try adding AccCom and CCLevel2 as columns too, to see if that gives any clues. Otherwise, I think I am out of ideas...