Forum Discussion

domc23's avatar
domc23
Icon for Helper I rankHelper 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 c...
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

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft 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
    Icon for Helper I rankHelper 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
      Icon for Microsoft Employee rankMicrosoft 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

      • domc23's avatar
        domc23
        Icon for Helper I rankHelper I
        8 years ago

        Thank you for your respose. So I would use both group number and name from Table1 and then both the group name and number from Table2 even though I am trying to join / merge only the group name and employer name from the two Tables? I will give it a try and see what happens. ,