Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 |
Solved! Go to 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
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 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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
10 | |
8 | |
7 |
User | Count |
---|---|
17 | |
13 | |
7 | |
6 | |
6 |