New Table duplicate rows created based on list of all distinct values from other table column

[arcanri]

Hello,

I have a price index table (Price Index) that contains price indexes for various categories of price indexes by date.  I also have a formulas table that contains formula parameters for each category and also a formula.  I have the Price Index table and Formulas table related through a Category joiner table.  However, there isn't a relationship between Formula on the Formulas table to the Price Index table.  Ultimately, I would like to create a new table that calculates the Price from the price index utilizing the formula parameters from the Formulas table.  To do so, I'm trying to duplicate the rows of the price index table for each Formula in the Formulas Table.  I should also mention that I will likely add new Formulas over time so I would like this to be a dynamic process where if I add a formula to the Formulas table, new records will be created in teh desired table.  Please see sample tables below:

Price Index

Date	Category	Price Index
1/20/2023	A	100
1/20/2023	B	90
4/15/2023	A	200
4/15/2023	B	175
1/1/2024	A	300
1/1/2024	B	250

Formulas Table

Formula	Category	Intercept	Slope
Alpha	A	-100	1.5
Alpha	B	-90	1.5
Zulu	A	-110	1.2
Zulu	B	-105	1.1

Desired Table

Date	Category	Price Index	Formula Name	Calculated Price
1/20/2023	A	100	Alpha	150
1/20/2023	B	90	Alpha	175
4/15/2023	A	200	Alpha	.
4/15/2023	B	175	Alpha	.
1/1/2024	A	300	Alpha	.
1/1/2024	B	250	Alpha	.
1/20/2023	A	100	Zulu	125
1/20/2023	B	90	Zulu	110
4/15/2023	A	200	Zulu	.
4/15/2023	B	175	Zulu	.
1/1/2024	A	300	Zulu	.
1/1/2024	B	250	Zulu	.

Thank you!

[hnguy71]

Hi @arcanri 

Not sure how you're obtaining the calculated price as it doesn't match your slope value. Either way, you can do this via PowerQuery.

Step 01: Merge category to category:

Step 02: Expand the Formula name and Slope value:

Step 03: Add a new calculated column to get your new price:

Desired output:

[arcanri]

Thank you.  Just running into one issue. The Price Index table is a calculated table utilizing dax.  Therefore, I don't see it in Power Query Editor.  Is there any way to import the table into Power Query Editor? Thanks!

[Anonymous]

Hi @arcanri ,

The measure and calculated columns are in DAX , which is M script in power query, so you can't see it in power query. But you can create index columns with built-in functions.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[hnguy71]

Hi @arcanri 

Joins like this is usually accomplished as part of an ETL process but it's possible to achieve this within DAX if you have a calculated table.

Try this instead:

CrossJoin_Table = 

VAR _Base = 
SELECTCOLUMNS(
    FILTER(
        CROSSJOIN(
            SELECTCOLUMNS(Formulas, 
                "f.Formula", Formulas[Formula], 
                "f.Category", Formulas[Category], 
                "f.Intercept", Formulas[Intercept], 
                "f.Slope", Formulas[Slope]
            ),
            SELECTCOLUMNS(PriceIndex, 
                "p.Date", PriceIndex[Date], 
                "p.Category", PriceIndex[Category], 
                "p.Index", PriceIndex[Price Index]
            )
        ),
        [f.Category] = [p.Category]
    ),
    "Formula", [f.Formula],
    "Category", [p.Category],
    "Intercept", [f.Intercept],
    "Slope", [f.Slope],
    "Date", [p.Date],
    "Price Index", [p.Index]
)

VAR _Calculate = 
ADDCOLUMNS( _Base, "CalculatedPrice", [Price Index] * [Slope])

RETURN

_Calculate