Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have two queries from two different sources. There are a couple overlapping columns from each that i'm attempting to use to combine them into one table. Basically I need all the info from both in order to do some comparison. For the sake of discretion, here are sample tables that convey what I'm trying to do:
Recipe Table - Used to quantify how much is supposed to be used for each product
Product | Ingredient | Required |
Cake | Chocolate | 5 |
Cake | Egg | 1 |
Cake | Flour | 3 |
Cake | Sugar | 1 |
Cake | Water | 0.5 |
Muffin | Cinnamon | 0.1 |
Muffin | Egg | 2 |
Muffin | Flour | 4 |
Muffin | Sugar | 2 |
Muffin | Water | 0.5 |
Actual Use Data - what the new baker actually used by invoice
Invoice | Product | Ingredient | Actual |
A | Muffin | Egg | 3 |
A | Muffin | Sugar | 2 |
A | Muffin | Water | 0.5 |
B | Cake | Chocolate | 5 |
B | Cake | Egg | 1 |
B | Cake | Flour | 1 |
C | Muffin | Chocolate | 2 |
C | Muffin | Sugar | 1 |
C | Muffin | Water | 1 |
End Result I'm looking for:
Invoice | Product | Ingredient | Actual | Required |
A | Muffin | Cinnamon | 0 | 0.1 |
A | Muffin | Egg | 3 | 2 |
A | Muffin | Flour | 0 | 4 |
A | Muffin | Sugar | 2 | 2 |
A | Muffin | Water | 0.5 | 0.5 |
B | Cake | Chocolate | 5 | 5 |
B | Cake | Egg | 1 | 1 |
B | Cake | Flour | 1 | 3 |
B | Cake | Sugar | 0 | 1 |
B | Cake | Water | 0 | 0.5 |
C | Muffin | Chocolate | 2 | 0 |
C | Muffin | Cinnamon | 0 | 0.1 |
C | Muffin | Egg | 0 | 2 |
C | Muffin | Flour | 0 | 4 |
C | Muffin | Sugar | 1 | 2 |
C | Muffin | Water | 1 | 0.5 |
So, I'm trying to create rows for each of the following:
1. Matching data in both tables
2. Ingredients not used that are in the recipe (Actual = null and Required = something)
3. Ingredients used that are not in the recipe (Required = null and Actual = something)
I've been able to create #1 & #3 with a left outer join. I've tried multiple combinations of joins and anti-joins to create #2, but have been unsuccessful. No matter what I try, i can't associate an invoice to something with no Actual data. How can I accomplish this?
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
69 | |
38 | |
26 | |
26 |
User | Count |
---|---|
97 | |
96 | |
59 | |
44 | |
40 |