Forum Discussion

ieatwater's avatar
ieatwater
New Member
2 years ago
Solved

How to concat multiple columns value by each row

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 ...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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
dufoq3's avatar
dufoq3
Community Champion
2 years ago

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