Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jsparrow88
New Member

from 1 columns create others colums every 34 rows

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

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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_

 

SU18_powerbi_badge

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.

 

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

@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_

 

 

SU18_powerbi_badge

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
Super User
Super User

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_

 

SU18_powerbi_badge

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.

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors