Adding Column that Assigns fixed value based on another colu

[Anonymous]

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: (fake data used for illustration)

This will allow me to use CALCULATE(SUM() to capture the average selling price of an Order, based on the Mattress sold within it. 

I feel like this is easy, yet I don't know how to do it. I am hooked up using Direct Query to Azure, into our data warehouse.

I know Direct Query comes with some limitations. I tried duplicating the table, and filtering down to ONLY mattresses, and then doing a query merge between the original and the new to pull in the Mattress sold within each order. But I don't know if this is working and I feel there has to be a better way. 

Thank you!

[v-jingzhang]

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.

[jennratten]

Hello - there are a few different ways of accomplishing this. Here are a couple of options:

Option #1

Create a reference query with distinct rows for only the order number and mattress type columns, then merge with the primary query.

OrderDetails

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVtJR8k0sKSlKLS4GMiNAfGMDAwOlWB0kFU6JxalAyjElq7S4JDEpB8QxMkVT5FZalJdZUloEkvSIKTUwMDJzA5mGri4gMycnvxxkmzNI3txUKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, Product = _t, #"Product Type" = _t, #"Selling Price" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", type text}, {"Product", type text}, {"Product Type", type text}, {"Selling Price", Int64.Type}})
in
    #"Changed Type"

OrderMattressTypes

let
    Source = OrderDetails,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Product] = "Mattress")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Order", "Product Type"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

OrderDetailsWithMattressType

let
    Source = Table.NestedJoin(OrderDetails, {"Order"}, OrderMattressTypes, {"Order"}, "OrderMattressTypes", JoinKind.LeftOuter),
    #"Expanded OrderMattressTypes" = Table.ExpandTableColumn(Source, "OrderMattressTypes", {"Product Type"}, {"Mattress Type"})
in
    #"Expanded OrderMattressTypes"

Option #2

Create a mattress types dimension table, create a relationship between the tables in the data model. Use the dimension table to filter the measure.

Same OrderDetails table as listed in Option #1.

MattressTypes

let
    Source = OrderDetails,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Product", "Product Type"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Product] = "Mattress")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows")
in
    #"Removed Duplicates"