Help to create a data model with Budget and Actuals

Question

Hi All,&nbsp;I want to create a data model for the following:&nbsp;I extract data from a software giving me the revenu for each product and customer. I have also a budget file I built that gives me the budgeted revenue per customer and product.&nbsp;I want to create 4 tables:&nbsp;- Product Group table that give all the possible product group in the revenue and budget : sometimes, we have a product group in the budget we haven't sold yet in the actuals. It is also true the other way. We sometimes sell products that were not budgeted.&nbsp;- Customer Table gives all the possible customers in the revenue and budget. We have budgeted customers where no revenue is made and therefore will not appear in the actuals extract. The other way around is true when we have new customers not budgeted.&nbsp;- Fact Table: where I have the extract from the tool giving me the actuals- Fact Budget: my excel file showing the detailed budgeted revenu per customer and product&nbsp;My biggest challenge is to create the product and customer tables . I want to base my liste of unique value from the actuals table AND the budget table to make sure I use all possible items.&nbsp;Could you please help me with this?&nbsp;

anonymous · Accepted Answer

Hi&nbsp;Tarek78210&nbsp;
You can create two blank queries and input the following code to advanced editor&nbsp;
Product
let
    actual_product=Table.FromList(actuals[ProductGroup]),
    budget_product=Table.FromList(budget[ProductGroup]),
    _combine=Table.Combine({actual_product,budget_product}),
    _distinct=Table.Distinct(_combine)
in
    _distinct
Output

Customer
let
    actual_custromer=Table.FromList(actuals[Customer]),
    budget_customer=Table.FromList(budget[Customer]),
    _combine=Table.Combine({ actual_custromer,budget_customer}),
    _distinct=Table.Distinct(_combine)
in
    _distinct
&nbsp;Output

&nbsp;
&nbsp;
&nbsp;
Best Regards!
Yolo Zhu
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;

Forum Discussion

Related content

Budget, actual and variance matrix

Budget vs Actual

Budget vs Actuals Dashboard Best Practices

Budget vs Actual Help

Budget vs. Actuals