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
jennratten's avatar
jennratten
Super User
4 years ago

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"