Forum Discussion
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 with object along the top and size along the bottom, in an Excel spreadsheet). What is the best way to generate a cost column, in a way that makes it easy to change the cost parameter if necessary?
I appologize if this is posted twice - I'll delete the other post if so.
Thank you.
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
8 Replies
Hi Anonymous
You can do this by performing a merge operation with Power Query, here is how: https://support.office.com/en-us/article/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d17f9
If I understand correctly, you may first need to unpivot the table coming from the Excel spreadsheet
Hi Anonymous,
I have a cost table with object along the top and size along the bottom
How is this table like? Please show us an example.
What is the best way to generate a cost column, in a way that makes it easy to change the cost parameter if necessary
Do you want a cost column which is dynamically calculated based on slicer selection? What value should be slicer items?
Please provide sample data and expected result to make your requirement more clear. How to Get Your Question Answered Quickly
Best regards,
Yuliana Gu
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.
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
Hi sailkitty. I took a look at what if parameters, and I don't think that's what I'm looking for. I do not need user input. I need a custom column that pulls a cost from a lookup table based on the fruit and size column. I would like the lookup table to be editable in query editor. Please let me know if I misunderstood. Thanks though.
