Conditional column which merges all coincident values

[alameda]

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

[Greg_Deckler]

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.

[artemus · Accepted solution]

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"

[alameda]

It works!!! 😊😊😊

Thank you so much.