Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 Name | License Name | Quantity |
Customer A | License 1 | 18 |
Customer A | License 2 | 38 |
Customer B | License 2 | 13 |
Customer B | License 3 | 2 |
Customer C | License 4 | 24 |
Customer C | License 5 | 29 |
Customer C | License 6 | 28 |
spreadsheet 2:
Customer Name | License Name | Quantity |
Customer A | License 1 | 19 |
Customer A | License 2 | 39 |
Customer B | License 2 | 13 |
Customer B | License 3 | 3 |
Customer C | License 4 | 24 |
Customer C | License 5 | 32 |
Customer C | License 6 | 2 |
Customer C | License 7 | 29 |
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 Name | License Name | Quantity | Quantity |
Customer A | License 1 | 18 | 322 |
Customer A | License 2 | 38 | 829 |
Customer B | License 2 | 13 | 829 |
Customer B | License 3 | 2 | 78 |
Customer C | License 4 | 24 | 73 |
Customer C | License 5 | 29 | 0 |
Customer C | License 6 | 28 | 1613 |
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?!
Solved! Go to Solution.
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
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
@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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |