Forum Discussion
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 automatically? i.e. if there is an A6, then col. C automatically populates A6B1, A6B2, A6B3...
Thanks in advance.
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
2 Replies
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
