How to split a list into table (each nth row and continue in next column)

[Anonymous]

I have a use case in which I'm getting the data in rather unformatted way. However it can be tackled by understanding the structure. Kind of a "you just need to know" way, not ideal I know. 

But this has brought me a challenge that I have a list of data i.e.

12312,

1231235,

540945,

4506,

2349,

2349,

13409,

3490834,

1349,

3490835,

309034,

2394,

110,

124349,

...

What I need to achieve with this list is to create a table out of it and as an example the above example needs to be formatted to table in a way that first 3 values should be 1st row, then the values from 4-6 need to be second row and so on.

Result should be:

12312,1231235,540945

4506,2349,2349

13409,3490834,1349

3490835,309034,2394

110,124349

[AlB · Accepted solution]

Hi @Anonymous 

Use List.Split. Place this code in a blank query to see the steps from your initial example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc3BDcAwCAPAXfLuw2CnKrNE2X+NRoRI7e9kDIzRzGne5lViT3chtKmOO+FU/GFctdRK8FAn/Yb7ChGouTOqaKjHypX5Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", Int64.Type}}),

    list_ = List.Split(#"Changed Type"[Col1],3),
    #"Converted to Table" = Table.FromList(list_, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Extracted Values"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Great that actually works! At least with that I can do the necessary tranformations - thanks!