Forum Discussion
Add a conditional column with multiple conditions
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 |
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 TeamIf 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
2 Replies
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 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 TeamIf 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