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

Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.

Reply
Nissim-Elaluf
Regular Visitor

Pivoting ? transpos?

I would appreciate help:

that the values ​​in column 1 be the name of the columns
But every 6 rows will feed the new values ​​in a new row

For example in the attached example there will be 4 lines

NissimElaluf_0-1652693031060.png

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

TRANSPOSE APPROACH - See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc9LCsAwCEXRvWTcgcbkqcN+NxG6/21UQsEML8LhOUbZy1Z2p1rebZQj4gALzzqjTuC/XVGXGWjWHXVXZpv1RD3C3mZNEgxNs4u2NLXaYmrrWExTStNYfTHhy84KosUUR5rOpGkKC9Ls7PHf+wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Temp", each Text.Combine([Column2],";"), type nullable text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Temp", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Temp.1", "Temp.2", "Temp.3"}),
    #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

PIVOT APPROACH

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc9LCsAwCEXRvWTcgcbkqcN+NxG6/21UQsEML8LhOUbZy1Z2p1rebZQj4gALzzqjTuC/XVGXGWjWHXVXZpv1RD3C3mZNEgxNs4u2NLXaYmrrWExTStNYfTHhy84KosUUR5rOpGkKC9Ls7PHf+wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Temp", each _, type table [Column1=nullable text, Column2=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Temp],"Index")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Temp", "Column1"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2", "Index"}, {"Column1", "Column2", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Column1]), "Column1", "Column2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

 

View solution in original post

1 REPLY 1
Vijay_A_Verma
Super User
Super User

TRANSPOSE APPROACH - See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc9LCsAwCEXRvWTcgcbkqcN+NxG6/21UQsEML8LhOUbZy1Z2p1rebZQj4gALzzqjTuC/XVGXGWjWHXVXZpv1RD3C3mZNEgxNs4u2NLXaYmrrWExTStNYfTHhy84KosUUR5rOpGkKC9Ls7PHf+wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Temp", each Text.Combine([Column2],";"), type nullable text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Temp", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Temp.1", "Temp.2", "Temp.3"}),
    #"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

PIVOT APPROACH

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc9LCsAwCEXRvWTcgcbkqcN+NxG6/21UQsEML8LhOUbZy1Z2p1rebZQj4gALzzqjTuC/XVGXGWjWHXVXZpv1RD3C3mZNEgxNs4u2NLXaYmrrWExTStNYfTHhy84KosUUR5rOpGkKC9Ls7PHf+wE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Temp", each _, type table [Column1=nullable text, Column2=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Temp],"Index")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Temp", "Column1"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2", "Index"}, {"Column1", "Column2", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Column1]), "Column1", "Column2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

 

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors