How to concat multiple columns value by each row

[ieatwater]

Hi community,

I'm trying to concat col. A and col. B by each distinct value of each row (result = col. C).

How can I use power query or formula to do this, and capture new items in col. A and B automatically? i.e. if there is an A6, then col. C automatically populates A6B1, A6B2, A6B3...

Thanks in advance.

[slorin]

Hi @ieatwater 

With Excel

=TOCOL( Range_A & TRANSPOSE( Range_B ))

=TOCOL( A1:INDEX(A:A,COUNTA(A:A)) & TRANSPOSE( B1:INDEX(B:B,COUNTA(B:B)) ))

=TOCOL( Table1[Column1] & TRANSPOSE( Table2[Column2] ))

Stéphane

[dufoq3 · Accepted solution]

Hi @ieatwater,

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lFyMlSK1QGyjUBsIwjbGMQ2hrBNQGwTCNsUxDZVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    Combined = List.TransformMany(
        {0..List.Count(Source[Column1])-1},
        each Source[Column2],
        (x,y)=> Source[Column1]{x} & y
),
    ToTable = Table.FromList(Combined, (x)=> {x}, type table[Combined=text])
in
    ToTable