Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
domc23
Helper I
Helper I

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!

1 ACCEPTED SOLUTION

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. Smiley Happy

 

rl1.PNG

 

Regards

View solution in original post

9 REPLIES 9
v-ljerr-msft
Employee
Employee

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? Smiley Happy

 

Regards

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                   

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. Smiley Happy

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]
        )
    )
)

t3.PNG

 

Regards

I got it to work. I corrected my misktake in the code line and I am getting all the group names and group numbers I believe. Part of the problem was I had the Data Load set to automatic type detection which was causing an issue with alphanumeric group numbers. That has been resolved.

 

I used your code to create a new table that has the group numbers and group names combined as best as I can tell. There is a lot of data.

 

My new issue is when I try to make a relationship between the new table I have created (Group List) to the two original tables (Claims Audit 2018 and ClaimsSLAReport) between the group number and group name columns, Power BI will do nothing for about a minute, and then crash.

 

Any thoughts on why this would be happening? SO frustrating to be this close but to not be able to get over the finish line.

 

HELP!!!

 

Thank you!

I have resolved the issue of Power BI crashing when I attempt to create a releationship between the new table that was created and the existing tables by updating to the January 2018 of Power BI Desktop.

 

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.

 

Any help / suggestions is appreciated.

 

Thank you

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. Smiley Happy

 

rl1.PNG

 

Regards

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. ,

Thank you again!

 

This worked, sort of. For some reason, some of the group numbers are actually the group names. Most are as expected. Does the code look correct above or did I make an error in it? I am double checking the source data to confirm that there should be a group name and a group number. There may be cases where there is not a group number listed. Would that cause any issues?

 

Here is the code I used

Group List = DISTINCT(UNION(SELECTCOLUMNS('Claims Audit 2018',"Group #",'Claims Audit 2018'[Group #],"Group Name",'Claims Audit 2018'[Group Name]),SELECTCOLUMNS(ClaimsSLAReport,"EmployerNumber",ClaimsSLAReport[EmployerName],"EmployerName",ClaimsSLAReport[EmployerName])))

 

 

Disregard my last post. I caught the error I made in the code I tried. I resolved that issue.

 

Thank you

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.