• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power query : concat cells of a table column

Lolo

Member
Hello all,

I'm wondering how, in a M script (Power Query), I could split an excel table like the following

COL
A
B
C
D
E
F
G
H

and concat each cell content 3 by 3 (with a comma separator).
Result expected : a new table like this (that I could reuse in the next steps of the script)

COL RESULT
A,B,C
D,E,F
G,H

Is it possible to do that ?
Thank you :)
 
You need to give us bit more than that.

Are there any other column(s) in original table? Or is it just single column table that you are trying to transform?

Is there a possibility of duplicates in the COL?
 
If you are only interested in said column and nothing else. You could do something like below.

1. Load the table (Table1) to PQ, add index column (From 1)
2. Add custom column with following formula.
Code:
=Number.RoundUp([Index]/3)
3. Add new blank query (Query1). In formula pane enter following and rename the column to "Num"
Code:
= Table.FromList(List.Generate(()=>Table.RowCount(Table1), each _ >0, each _ - 1),Splitter.SplitByNothing(),null,null,ExtraValues.Ignore)
4. Sort column ascending.
5. Merge Table1 to Query1, using InnerJoin. Using "Num" from Query1 and "Custom" from Table1 as key.
6. Add custom column to Query1. With following formula.
Code:
=Text.Combine([NewColumn][COL], ",")
7. Remove unnecessary columns, change column data type to Text and rename column as needed.

Result:
upload_2017-7-11_14-49-8.png
 
Back
Top