Forum Discussion
I'm unable to structure Power Query Table in a way to get my desired result.
I'd transform Query2 to
(query Receipts)
Next, "Receipts produced" as
Join first with second ("Produced") to have details
Combine it with query 1 (Ingredient Price) to receive price per day ("Prices")
Join Produced with Prices to have final costs
And be sure you have same names among all source tables, I guess "Almond" and "Almonds" shall mean the same.
It's hard to test since data in samples is not linked. Perhaps (see attached)
- append Q3 and Q1
- sort by product and date
- group by product without aggregation
- fill up/down the price within groups
- expand and calculate cost
- pivot products
Hi sir, SergeiBaklan
There's a main component you have missed. Which is Query 2.
Which is the recipe.
Also the components of Query1 are ingredients used in Recipe(Query 2) to make the Product (Query3)
The Final solution would be some kind of SUMPRODUCT of Query1 * Query2 * Query3
I've renamed the headers to make the concept clearer. I'm attaching the updated excel file and I've also done a breakdown of how we'll arrive at the final solution.
Thank you again for looking into it. It's been super confusing and mind boggling for me.
I'd transform Query2 to
(query Receipts)
Next, "Receipts produced" as
Join first with second ("Produced") to have details
Combine it with query 1 (Ingredient Price) to receive price per day ("Prices")
Join Produced with Prices to have final costs
And be sure you have same names among all source tables, I guess "Almond" and "Almonds" shall mean the same.
This is just spectacular sir. SergeiBaklan
With my limited knowledge of power query, I was about to use transpose and sumproduct function on the raw imported queries.
I'm an Ice cream manufacturer in India. If you ever visit, you have free lifetime access to it 🙂Thank you, appreciate. Glad it helped.