Add a conditional column with multiple conditions

[Anonymous]

Hello all,
I have a dataset with a top material number, in another column the component number that goes into the top material and a calculation number.
I would like to generate a new column in PowerQuery. This column should basically reflect the top material. However, if the top material corresponds to the component, the value in the new column for this calculation number should remain empty or zero.

In this case, the value zero would have to be displayed in two rows, because both rows have the same Calc number.

Top Material	Komp	Calc Nr	Expected Column
0000221100	0000221100	000105911604	null
0000221100		000105911604	null
0923148340502		000107570064	0923148340502
0923148340502		000107570064	0923148340502
0923148340502		000107570064	0923148340502
0923148340502	0923-0016	000107570064	0923148340502
0923148340502	0088009200	000107570064	0923148340502
0923148340502		000107570064	0923148340502

[Anonymous]

Hello all,
I have a dataset with a top material number, in another column the component number that goes into the top material and a calculation number.
I would like to generate a new column in PowerQuery. This column should basically reflect the top material. However, if the top material corresponds to the component, the value in the new column for this calculation number should remain empty or zero.

In this case, the value zero would have to be displayed in two rows, because both rows have the same Calc number.

Top Material	Komp	Calc Nr	Expected New Column Material
0000221100	0000221100	000105911604	null
0000221100		000105911604	null
0965022511302		000110870745	0965022511302
0965022511302		000110870745	0965022511302
0965022511302		000110870745	0965022511302
0965022511302		000110870745	0965022511302
0965022511302		000110870745	0965022511302
0965022511302		000110870745	0965022511302
0965022511302	0080241100	000110870745	0965022511302
0965022511302		000110870745	0965022511302

[v-cgao-msft · Accepted solution]

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