Reply
Regular Visitor
Posts: 16
Registered: ‎03-03-2017
Accepted Solution

Joining multiple tables/queries

Hi,

 

I'm new to Power BI (currently going through the videos on edX). I have a long background with Crystal Reports and now trying to slowly convert that knowledge into PBI.

 

One of the things that I'm trying to do is the following:

- I have a SQL Server database that has two tables, a customer table and an invoice table

- I'd like to bring these into PBI together to build some dashboards, reports, etc.

- The primary keys in both are a Company ID and a Customer ID

 

I've used the Get Data process to bring in each of the tables in as separate queries. But when I try to use the Relationships function, I get this error message:

You can't create a relationship between these two columns because one of the columns must have unique values

 

Should I do this in a different way? or am I missing something?


Accepted Solutions
Super User
Posts: 2,618
Registered: ‎11-29-2015

Re: Joining multiple tables/queries

This will be the reason for the error.

 

One option is to create a new column that combine your CompanyID/CustomerID into a single column, which you can do in the Query Editor.  This would need to be done in both tables to create the relationship.

 

Otherwise, you could combine both tables into 1 in Query Editor using the Merge command to create a table with the following columns

 

CompanyID , CustomerID , CustomerName , InvoiceNum

 

Cheers,

 

Phil

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Regular Visitor
Posts: 19
Registered: ‎12-05-2016

Re: Joining multiple tables/queries

I think you're actually better off using that first suggestion - keep the two separate tables, but create concatenated key columns in both tables that combine the CompanyID and CustomerID columns, using these new columns to join the tables. This allows you to treat the customer table as a lookup table. As you start learning more about PowerBI, you'll see that this is the standard modelling approach.

View solution in original post


All Replies
Super User
Posts: 2,618
Registered: ‎11-29-2015

Re: Joining multiple tables/queries

Hi @Machzy

 

Does your Customer table have only 1 row per Customer?  No blanks or duplicates in the CustomerID column (which is what I presume you are linking on).

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Regular Visitor
Posts: 16
Registered: ‎03-03-2017

Re: Joining multiple tables/queries

No, if you want no duplicates, it would have to be a combination of the Company ID & the Customer ID for the customer table. And Company ID & Invoice Number for the invoice table.

 

Attached is an example.CustInvs.png

 

 

 

Super User
Posts: 2,618
Registered: ‎11-29-2015

Re: Joining multiple tables/queries

This will be the reason for the error.

 

One option is to create a new column that combine your CompanyID/CustomerID into a single column, which you can do in the Query Editor.  This would need to be done in both tables to create the relationship.

 

Otherwise, you could combine both tables into 1 in Query Editor using the Merge command to create a table with the following columns

 

CompanyID , CustomerID , CustomerName , InvoiceNum

 

Cheers,

 

Phil

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Regular Visitor
Posts: 16
Registered: ‎03-03-2017

Re: Joining multiple tables/queries

Yeah, I was hoping I wouldn't have to use the Merge function, but it worked. 

 

Another question if you don't mind. I had the Customers and Invoices queries separate, but after the merge (I merged using the Invoices query) the customer query is still there but when I try to delete it, I get this error message:

 

The query 'Customers' cannot be deleted because it's being referenced by another query: 'Invoices'

 

Is there a way that I can delete it? or do I have to leave it there?


Super User
Posts: 2,618
Registered: ‎11-29-2015

Re: Joining multiple tables/queries

You might be able to right click the table in the Query Editor and tell it not to refresh or load to the model.  In which case it only exists to get data from your source into Power Query and deliver it to the merge.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Regular Visitor
Posts: 16
Registered: ‎03-03-2017

Re: Joining multiple tables/queries

I did both and it didn't work, looks like it really wants to keep it in there. Oh well.

 

Thanks for the help.

Regular Visitor
Posts: 19
Registered: ‎12-05-2016

Re: Joining multiple tables/queries

I think you're actually better off using that first suggestion - keep the two separate tables, but create concatenated key columns in both tables that combine the CompanyID and CustomerID columns, using these new columns to join the tables. This allows you to treat the customer table as a lookup table. As you start learning more about PowerBI, you'll see that this is the standard modelling approach.

Regular Visitor
Posts: 16
Registered: ‎03-03-2017

Re: Joining multiple tables/queries

Switched to this, seems like a better strategy.

 

Thanks for the tip!