Forum Discussion
I'm unable to structure Power Query Table in a way to get my desired result.
I have 3 tables coming from Power Query.
1) Cost of ingredients which change with date
2) Fixed Recipes of product
3) Unit Produced of product date wise
I want to make a table of cost of product as per the date
I'm attaching the file as well
PeterBartholomew1 SergeiBaklan Detlef_Lewin Riny_van_Eekelen
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.
9 Replies
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.
First you should change Query2 and Query3 to get an output of Recipe-Ingredient-Units for Query2 and Date-Ingredient-Units for Query3.
- I've added dummy raw data for Query2, in the file I attached.
I can't understand how to structure it. Can you help me with that.
Query3 is data entry of how many units are produces, there isn't a need for ingredients there.
