Forum Discussion
Helper IIIPower Query: add distinct cell values from table column as new column
Hello community,
I am struggling with the following objective:
I have a table with repeating name values and want those distinct names to appear as a new column with the distinct names "filled from top" leaving empty row cell values.
Example:
| Index | Name | ExpectedResult |
| 1 | A_A | A_A |
| 1 | A_A | B_A |
| 2 | B_A | B_B |
| 3 | B_B | C_A |
| 4 | C_A | D_A |
| 4 | C_A | |
| 4 | C_A | |
| 5 | D_A | |
| 5 | D_A |
I grouped by Name to create the Index column (not sure if it is even needed for this). Now I want to create a new column that only contains the distinct Name column values (as shown in the ExpectedResult column).
I tried by creating a new query and merging but I couldn't get the expected result. I believe this should work with an if-stmt but I wasn't able to construct it.
Hope you can give me some ideas.
Thank you.
pls try this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXKMd1SK1UFlGwHZTlC2MZjtBGabANnOUHFcbFMg2wWdHQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Name", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "ExpectedResult", each try List.Distinct( #"Added Index"[Name]){[Index.1]} otherwise null) in #"Added Custom"
3 Replies
- Ahmedx
Super User
pls try this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXKMd1SK1UFlGwHZTlC2MZjtBGabANnOUHFcbFMg2wWdHQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Name", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "ExpectedResult", each try List.Distinct( #"Added Index"[Name]){[Index.1]} otherwise null) in #"Added Custom"
tonycliftonPerfect and so simple.
Thank you Ahmedx
- Ahmedx
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.