Is it possible to make a new table that is based on data of two other tables in PowerBI?

[kevardll]

Hi guys,

I have the following two tables:

table 1 (Excel file 1):

customerID      yearly_forecast
324343             68400
753454             30000

table 2 (Excel file 2):

year           month              percentage
2020              10                 7%
2020              11                 7%
2020              12                 9%
2021              01                 11%
2021              02                 12%

New table needs to get the data as followed (example data):

customerID    percentage       yearly_forecast     monthly_forecast      year         month
3234343         7%                     68400                    4788         2020        10
3234343         7%                     68400                    4788         2020        11
3234343         9%                     68400                    5444         2020        12
3234343         11%                    68400                    5444         2020        12
3234343         9%                     68400                    5444         2020        12
753454          7%                     30000                    2533         2020        10
753454          7%                     30000                    2533         2020        11

etc..

If anyone knows whether this is possible and how to produce it in PowerBI I would be lifetime thankful 🙂

[Rayshin · Accepted solution]

@kevardll 

yes possible.  it's a very common pattern
https://www.daxpatterns.com/budget/
need a year column or customerid column to use as the join condition
If they both have a 'year' column, I think you're looking for
- Table 2 left outer join with table 1
- using the column year
You might not need the outer if there's no missing values