Forum Discussion
Solved
Conditional column which merges all coincident values
Hi:
I'm trying to get a conditional column which merges all the values from the column B which value in the column A is the same.
For example, mi desiredColumn would be something like this:
| columnA | columnB | desiredColumn |
| 1 | A | A |
| 2 | B | B | C |
| 2 | C | B | C |
| 3 | D | D | E | F |
| 3 | E | D | E | F |
| 3 | F | D | E | F |
| 4 | G | G |
I've been trying to work in something like this, but I don't know how to continue
let
Table = Table.FromRecords({[columnA = 1, columnB = "A"], [columnA = 2, columnB = "B"], [columnA = 2, columnB = "C"], [columnA = 3, columnB = "D"], [columnA = 3, columnB = "E"], [columnA = 3, columnB = "F"], [columnA = 4, columnB = "G"]}),
#"Group rows" = Table.Group(Table, {"columnA"}, {{"All", each _, type table [columnA=number, columnB=text]}, {"Distinct", each Table.RowCount(Table.Distinct(_)), type number}}),
#"Expand All" = Table.ExpandTableColumn(#"Group rows", "All", {"columnB"}, {"All.columnB"}),
#"Conditional column" = Table.AddColumn(#"Expand All", "desiredColumn", each if [Distinct] = 1 then [All.columnB] else "????")
in
#"Conditional column"
Any help will be appreciated.
César
You can do it more simply like:
let Source = #table(type table [columnA=Int32.Type, columnB=text], {{1, "A"}, {2, "B"}, {2, "C"}, {3, "D"}, {3, "E"}, {3, "F"}, {4, "G"}}), #"Added Custom" = Table.AddColumn(Source, "desiredColumn", each Text.Combine(Table.SelectRows(Source, (x) => [columnA] = x[columnA])[columnB], "|")) in #"Added Custom"
3 Replies
You can do it more simply like:
let Source = #table(type table [columnA=Int32.Type, columnB=text], {{1, "A"}, {2, "B"}, {2, "C"}, {3, "D"}, {3, "E"}, {3, "F"}, {4, "G"}}), #"Added Custom" = Table.AddColumn(Source, "desiredColumn", each Text.Combine(Table.SelectRows(Source, (x) => [columnA] = x[columnA])[columnB], "|")) in #"Added Custom"It works!!! 😊😊😊
Thank you so much.
Well, if it doesn't have to be Power Query, then the DAX is:
DAX Column = VAR __Table = FILTER(ALL('Table'),'Table'[columnA]=EARLIER('Table'[columnA])) RETURN CONCATENATEX(__Table,[columnB]," | ")If it has to be Power Query, ImkeF can likely tell you how to do it. I attached a PBIX.
