Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Referencing Parameter Table

Hi. I have a dataset with the two columns: object and size (queried from SQL Server). I would like to create a third column for cost. I know the cost based on the object and size (I have a cost table...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous,

     

    You can first unpivot the reference table in Query Editor to change its structure to below:

     

    Then, in source data table, add a calculated column with below DAX.

    Cost =
LOOKUPVALUE (
    'Reference Table'[Value],
    'Reference Table'[Fruit/Size], 'Source Table'[Fruit Sold],
    'Reference Table'[Type], 'Source Table'[Size]
)

     

    If you don't want to unpivot table, you can use below DAX to generate a cost column.

    Cost =
IF (
    'Source Table'[Size] = "Large",
    LOOKUPVALUE (
        'Reference Table'[Large],
        'Reference Table'[Fruit/Size], 'Source Table'[Fruit Sold]
    ),
    IF (
        'Source Table'[Size] = "Medium",
        LOOKUPVALUE (
            'Reference Table'[Medium],
            'Reference Table'[Fruit/Size], 'Source Table'[Fruit Sold]
        ),
        LOOKUPVALUE (
            'Reference Table'[Small],
            'Reference Table'[Fruit/Size], 'Source Table'[Fruit Sold]
        )
    )
)

    Best regards,

    Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi. Below is a simple example for the data table, where the first two columns are queried from a MSSQL database. I would like the cost column to be calculated, based on a lookup table that gives cost for a particular fruit and size. 

 

 

 

Below is an example of the lookup table I would use:

 

 

 

And here is my expected result (the cost column is calculated based on the first two columns using the lookup table):

 

 

 

Right now I am using many conditional statements to accomplish this. It would be nice if I could simple reference a lookup (second picture) table in Power Query M.

 

Thank you.

 

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

Hi Anonymous,

 

You can first unpivot the reference table in Query Editor to change its structure to below:

 

Then, in source data table, add a calculated column with below DAX.

Cost =
LOOKUPVALUE (
    'Reference Table'[Value],
    'Reference Table'[Fruit/Size], 'Source Table'[Fruit Sold],
    'Reference Table'[Type], 'Source Table'[Size]
)

 

If you don't want to unpivot table, you can use below DAX to generate a cost column.

Cost =
IF (
    'Source Table'[Size] = "Large",
    LOOKUPVALUE (
        'Reference Table'[Large],
        'Reference Table'[Fruit/Size], 'Source Table'[Fruit Sold]
    ),
    IF (
        'Source Table'[Size] = "Medium",
        LOOKUPVALUE (
            'Reference Table'[Medium],
            'Reference Table'[Fruit/Size], 'Source Table'[Fruit Sold]
        ),
        LOOKUPVALUE (
            'Reference Table'[Small],
            'Reference Table'[Fruit/Size], 'Source Table'[Fruit Sold]
        )
    )
)

Best regards,

Yuliana Gu