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
hmays
New Member

Comparing Two Simple Tables

Hi everyone, 

 

I'm trying to reconcile a spreadsheet of data with another spreadsheet of VERY similar data, but I'm having problems with relationships not filtering my quantites. 

For example:

 

spreadsheet 1: 

Customer NameLicense NameQuantity
Customer ALicense 118
Customer ALicense 238
Customer BLicense 213
Customer BLicense 32
Customer CLicense 424
Customer CLicense 529
Customer CLicense 628

 

spreadsheet 2:

Customer NameLicense NameQuantity
Customer ALicense 119
Customer ALicense 239
Customer BLicense 213
Customer BLicense 33
Customer CLicense 424
Customer CLicense 532
Customer CLicense 62
Customer CLicense 729

 

But when I input both tables in Power BI, and try and connect the quantities together (which is what I want to compare, I end up with this

Customer NameLicense NameQuantityQuantity
Customer ALicense 118322
Customer ALicense 238829
Customer BLicense 213829
Customer BLicense 3278
Customer CLicense 42473
Customer CLicense 5290
Customer CLicense 6281613

 

Basically, whenever I include a second quantity into the mix, it outputs the sum of all the specific licenses, not the quantity of each customer's particular amount of licenses. 

 

how do I make this happen?!

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

You could merge queries (as new) in Power Query editor.  That will allow you to 'inner join' on Customer and Licence fields.

After a bit more tinkering, you'll be left with a new table with the two quantity columns next to each other

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

You could merge queries (as new) in Power Query editor.  That will allow you to 'inner join' on Customer and Licence fields.

After a bit more tinkering, you'll be left with a new table with the two quantity columns next to each other

Anonymous
Not applicable

@HotChilli has a good solution - it will get you to where you want very quickly.

One consideration: You may want the Join Type to be Full Outer - that way you won't lose rows due to the data being not existing on the other table.

Cheers!

Nathan

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.