from 1 columns create others colums every 34 rows

[jsparrow88]

Hello,

i have only one column with a lot of rows, i would like create 1 column every 34 rows of the first column with power query.... after that i have to convert all columns, i have just created, in rows, so at the end the result will be: every row will have 34 columns.

thank you in advance for your help

[AlB]

@jsparrow88 

What has this got to do with the 34 rows?

Create a new query and substitute the code it has by the code below. See it all at work in the attached file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWCkgszQEzjE3AVFJ+aU5qWWJRikJxSWpqiYKRqQ5Y3Ce/WMExLz01J7UYzDcCk76JRckQrgWUX5mXk5gH0lwE0m1sYAkW90stV4jML8qG2AQ1sDQTYpQJRE1Yal5mcipMp5EpWDA4MU/BJTM1PV8pNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

    split_= List.Split(#"Changed Type"[Column1], 5),
    res_ = Table.Transpose(Table.FromColumns(split_))
in
    res_

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB · Accepted solution]

Hi @jsparrow88 

I am not sure what you would want this for but here's a way of doing it. Place the following M code in a blank query to see the steps. The two last steps are the relevant ones

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PdbLceNAEARRX3TeA6p6vrYw5L8bG0Jl8wKdmCGJ9Qbz+fzo5/ff58fvs97neJ/zfa73ud/neZ/3ferJj3xa+bjyeSWgFJSE0lAiSsWpmN8hFafiVJyKU3EqTsWpVCqVSvGnpFKpVCqVSqVSqVQqI5WRykhl8B9JZaQyUhmpjFRGKjOVmcpMZaYy+cemMlOZqcxUZiorlZXKSmWlslJZfD+prFRWKiuVncpOZaeyU9mp7FQ2X3MqO5WdyknlpHJSOamcVE4qJ5XDWlI5qdxUbio3lZvKTeWmclO5qVxG16tjdg+7exjew/IepvewvYfxPazvYX4Pve+M6fWQe8k95d5yj7nX3HNmz2LQcrugx6bFqMWqxazFrsWwxbLFtMW2VQ2NHvMW+xYDFwsXExcbFyMXKxcz12i59Fi6mLrYuhi7WLuYu9i7GLxYvGYfBfQYvVi9mL3YvRi+WL6Yvti+GL9Wny302L8AIAQIAsKAQCAUCAbCgXYfVvSgICwIDEKD4CA8CBBChCCh06cfPVQIFsKFgCFkCBrChsAhdOj2cdrnKQcqPowP48P4MD6MD+PD+DA+rD6g6eHD+DA+jA/jw/gwPtznfR/43xOfXp/5fej3qd/Hfp/7ffDjw/gwPlz9CqGHD+PD+DA+jA/jw/gwPowPj34n0cOH8WF8GB/Gh/FhfBgfxodnv+To4cP4MD6MD+PD+DA+jA/jw6vfmvTwYXwYH8aH8WF8GB/Gh/Hh3a9hevgwPowP48P4MD6MD+PD+PDp9zo9fBgfxofxYXwYH8aH8WF8+PZFoW8KXBXwUfgofBQ+Ch+Fj8JH4aPwUeqrBz18FD4KH4WPwkfho/BR+Ch8lPsuQw8fhY/CR+Gj8FH4qL4Z9dWo70bfyxG9vh71/agvSH1D6isSPurPx+9/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),

    split_= List.Split(#"Changed Type"[Column1], 34),
    res_ = Table.Transpose(Table.FromColumns(split_))
in
    res_

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[jsparrow88]

Hi super user,

thank you for your response, ill try to explain better what i want to do:

from 1 column like this:

1

Paul

34

boulevard steet 25,

Los Angeles

2

Marc

28

Maynland street 309

New York

3

Louis

49

Venice street 25

San Diego

i want to reach this result:

column A column B column C         column D                  column E

      1            Paul         34           boulevard steet 25,      Los Angeles

      2           Marc        28           Maynland street 309      New York

      3          Louise       49               Venice street 25          San Diego

pls i m a novice, specifie where i have to inser code step by step.

thank you again for your help