Add a conditional column with multiple conditions

Hi Anonymous ,

Please refer to the following steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMgACIyNDQwMDJR0MjqGBqaWhoZmBiVKsDppSBawqLI2MDU0sjE0MTA2MkBWZm5obGJjRWhGIrwtUZEaUYgMLCwOgIMKvpNkfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Top Material" = _t, Komp = _t, #"Calc Nr" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Calc Nr"}, {{"Data", each _}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{"Data",each Table.SelectRows(_,each [Komp]=[Top Material])}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"Calc Nr"}, {"Calc Nr.1"}),
    #"Merged Queries" = Table.NestedJoin(Source, {"Calc Nr"}, #"Expanded Data", {"Calc Nr.1"}, "Expanded Data", JoinKind.LeftOuter),
    #"Expanded Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Data", {"Calc Nr.1"}, {"Calc Nr.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Expanded Data", "Expected Column", each if [Calc Nr.1] <> null then null else [Top Material]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Calc Nr.1"})
in
    #"Removed Columns"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum