Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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.
Regards
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?
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 Number | Group Name |
1 | ROTARY INTERNATIONAL |
2 | KOBELT TRAVEL INC. |
3 | TRANSFORMER MANUFACT. INC. |
4 | LYONS FARRPORT MARKET |
5 | PETER TROOST MONUMENT |
6 | ITOFCA, INC. |
7 | NORTRAN |
8 | PARKER BROTHERS |
9 | BALESTRA'S |
10 | RAY KOONTZ |
11 | MITCHELL MILLER IGA |
Table 2 | |
Group Number | Employer Name |
2 | KOBELT TRAVEL INC. |
5 | PETER TROOST MONUMENT |
8 | PARKER BROTHERS |
9 | BALESTRA'S |
11 | MITCHELL MILLER IGA |
13 | CHICAGO GEAR WORKS |
22 | KNOX COUNTY EMPLOYEES |
44 | FLEMING/SLC DIVISION |
91 | VILLA SCALABRINI HOME |
101 | SCRIVNER/BUFFALO DIVISION |
211 | ANDERSON SHUMAKER COMPANY |
37 | MATHIEU IMPORTS |
Desired Results | |
Group Number | Group Name or Client Name |
1 | ROTARY INTERNATIONAL |
2 | KOBELT TRAVEL INC. |
3 | TRANSFORMER MANUFACT. INC. |
4 | LYONS FARRPORT MARKET |
5 | PETER TROOST MONUMENT |
6 | ITOFCA, INC. |
7 | NORTRAN |
8 | PARKER BROTHERS |
9 | BALESTRA'S |
10 | RAY KOONTZ |
11 | MITCHELL MILLER IGA |
2 | KOBELT TRAVEL INC. |
5 | PETER TROOST MONUMENT |
8 | PARKER BROTHERS |
9 | BALESTRA'S |
11 | MITCHELL MILLER IGA |
13 | CHICAGO GEAR WORKS |
22 | KNOX COUNTY EMPLOYEES |
44 | FLEMING/SLC DIVISION |
91 | VILLA SCALABRINI HOME |
101 | SCRIVNER/BUFFALO DIVISION |
211 | ANDERSON SHUMAKER COMPANY |
37 | MATHIEU 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.
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
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.
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |