Forum Discussion

Tarek78210's avatar
Tarek78210
Frequent Visitor
2 years ago
Solved

Help to create a data model with Budget and Actuals

Hi All,

 

I want to create a data model for the following:

 

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.

 

I want to create 4 tables:

 

- 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.

 

- 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.

 

- 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

 

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.

 

Could you please help me with this?

 

General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Tarek78210 

    You can create two blank queries and input the following code to advanced editor 

    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

     Output

     

     

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Tarek78210 

    You can create two blank queries and input the following code to advanced editor 

    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

     Output

     

     

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.