Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Adding Column that Assigns fixed value based on another colu

Hello,    I am inquiring on how to add a column in Power Query (within Power BI), that will assign each row of data to the "Mattress" sold for that "Order".      See Below on what I want: ...
Need Help
v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

Hi Anonymous 

 

Have you tried the merge queries method? Does it work?

 

Additionally, you can add a custom column with below code without a second table. 

Table.SelectRows(previousStep, (x)=> x[Order]=[Order] and x[Product]="Mattress"){0}[Product Type]

 

One concern is that you are using Direct Query, so this method may not be supported in Direct Query mode. If so, you will have to convert it to Import mode. 

 

As you said "This will allow me to use CALCULATE(SUM() to capture the average selling price of an Order, based on the Mattress sold within it. " Can you provide the expected output of the calculation so that we can think of DAX expressions supported in DirectQuery mode to calculate the result directly?

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.