Hi, I'm new to Power BI. I have 2 data files loaded as tables into Power Query, one is a listing of cars, the 2nd one is a listing of sales projections with a low, medium, and high sales prices for each specific car based on condition, type, etc. I have a join on the ID # between the 2 tables. However, what I really need is a 3rd table that pulls several columns from the first table (car listings table - i.e., ID #, make, model, year) and does some calculations on payments for those cars based on the sale price (sales projections)--i.e, if I select a 2005 Nissan Maxima from table 1, and then the "Medium" sale projection, I need to see what the estimated monthly payments would be. I can handle the calculation, I'm just wondering about how to create a new table to store the data from these tables. Or is this the right way to go about this? Ultimately, this will be displayed in Power BI visualizations.
Here's a sample of data
AUTOS (table)
AUTOSID | Year | Make | Model | Color
1 2006 Honda Civic White
2 2009 Kia Optima Red
SALES PRICES (AUTOSID is the link between the 2 tables)
SALESID | AUTOSID | Type | Low | Mid | High
1 1 2 DR 2500 4500 5000
2 1 4 DR 3000 3700 6000
3 2 2 DR 4000 4200 4800
4 2 4 DR 4500 5000 6500
Now, the table I don't have yet should look something like this...
PAYMENTS
PAYMENTID | AUTOSID | SALESID | Lease Term (Months) | Monthly (Calc)
1 1 1 30 120
2 1 2 30 124
3 2 1 30 124
So....
1) Do I create a "shell" spreadsheet CSV with the columns listed in the PAYMENTS table above or do I create the table on the fly in Power Query?
2) Is there a better way to do this? I'm still new, so I don't know if I should be creating the data on the fly here, or do it somewhere else and then just setup the column formulas and linking in Power Query for BI visualizations.
