Forum Discussion

domc23's avatar
domc23
Helper I
8 years ago
Solved

Connect columns from two separate data sources

Hello,

 

I have two separate data tables that I am trying to connect a column from each one so that I can filter on the data and it will control both tables. On the Claims Audit table, there is a column called Group Name and on the Claims SLA table, there is a column called Employer Name. I want to connect these two columns into one. 

 

On past dashboards, I simpy created a third table called Clients and populated that with a client list. This was done outside of Power BI on an Excel spreadsheet. I then imported that sheet into Power BI and them made my connection to each separate data source via that sheet. For some reason that is not working any more. And I would rather not do that because I would have to keep manually updating that main client list to keep it current. 

 

So how can I merge - append or join just the two columns "Group Name" and "Employer Name" from the two individual data sources??? Please help!

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi domc23,

    But I still cannot make any releationships between the newly created table and the original data source tables. I am getting the "You can't create a releationship between these two columns because one of the columns must have a unique values"!

     

    How can I get around this error. If I can get past this, I think I am home free.

    Based on my test, you should be able to use the [Group Name] (instead of [Group Number])column to make relationships between the newly created table and the original data source tables in your scenario. :smileyhappy:

     

     

    Regards

9 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi domc23,

     

    Could you post your table structures with some sample/mock data and the expected result, so that we can better assist on this issue? :smileyhappy:

     

    Regards

    • domc23's avatar
      domc23
      Helper I
      8 years ago

      Here is a very raw example of what I am trying to do. Combine or Append just the columns Employer Name and Group Name from the two separate tables. There will be duplicates on each list which is preventing me from linking the two data sources. Please excuse the way I posted these. I do not know how to get them side by side. I hope this makes some sense on what I am trying to accomplisy. Any help is greatly appreciated.

       

      Table 1 
        
      Group NumberGroup Name
      1ROTARY INTERNATIONAL               
      2KOBELT TRAVEL INC.                 
      3TRANSFORMER MANUFACT. INC.
      4LYONS FARRPORT MARKET
      5PETER TROOST MONUMENT             
      6ITOFCA, INC.                       
      7NORTRAN                           
      8PARKER BROTHERS               
      9BALESTRA'S                   
      10RAY KOONTZ                         
      11MITCHELL MILLER IGA           

       

      Table 2 
        
      Group NumberEmployer Name
      2KOBELT TRAVEL INC.                 
      5PETER TROOST MONUMENT             
      8PARKER BROTHERS               
      9BALESTRA'S                   
      11MITCHELL MILLER IGA           
      13CHICAGO GEAR WORKS                 
      22KNOX COUNTY EMPLOYEES             
      44FLEMING/SLC DIVISION               
      91VILLA SCALABRINI HOME             
      101SCRIVNER/BUFFALO DIVISION         
      211ANDERSON SHUMAKER COMPANY         
      37MATHIEU IMPORTS                   

       

      Desired Results 
        
      Group NumberGroup Name or Client Name
      1ROTARY INTERNATIONAL               
      2KOBELT TRAVEL INC.                 
      3TRANSFORMER MANUFACT. INC.
      4LYONS FARRPORT MARKET
      5PETER TROOST MONUMENT             
      6ITOFCA, INC.                       
      7NORTRAN                           
      8PARKER BROTHERS               
      9BALESTRA'S                   
      10RAY KOONTZ                         
      11MITCHELL MILLER IGA           
      2KOBELT TRAVEL INC.                 
      5PETER TROOST MONUMENT             
      8PARKER BROTHERS               
      9BALESTRA'S                   
      11MITCHELL MILLER IGA           
      13CHICAGO GEAR WORKS                 
      22KNOX COUNTY EMPLOYEES             
      44FLEMING/SLC DIVISION               
      91VILLA SCALABRINI HOME             
      101SCRIVNER/BUFFALO DIVISION
      211ANDERSON SHUMAKER COMPANY
      37MATHIEU IMPORTS                   
      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        Hi domc23,

         

        Based on my test, you should be able to use the formula below to create a new table to get the expected result in your scenario. :smileyhappy:

        Table =
DISTINCT (
    UNION (
        SELECTCOLUMNS (
            Table1,
            "Group Number", Table1[Group Number],
            "Group Name", Table1[Group Name]
        ),
        SELECTCOLUMNS (
            Table2,
            "Group Number", Table2[Group Number],
            "Group Name", Table2[Employer Name]
        )
    )
)

         

        Regards