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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
madrid
Helper I
Helper I

Transform blocks of rows into table

Dear all,

 

I have some data in format of block of rows. Is there any idea on how to transform this kind of data into table ?

See attached picture for demonstration.

 

Thank you,

madrid

 

madrid_0-1653128151479.png

 

1 ACCEPTED SOLUTION
ronrsnfld
Community Champion
Community Champion

If the rows will always be in groups of four, you can

  • Add an index column where each group of rows has an assigned number
    • Without this unique identifier for each block, the Pivot with no aggregation will error
  • Pivot on Column 1 with Column2 values and no aggregation

 

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

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),

    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Column1]), "Column1", "Column2"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

 

The above code assumes each block has four rows.  If the numbers of rows might vary, but, for example, the first row is always "Name", then you can still assign a unique number to each block with the code below, and then do your pivot as before.

 

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Column1] = "Name" then [Index] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"Custom"}),

 

 

View solution in original post

4 REPLIES 4
ronrsnfld
Community Champion
Community Champion

If the rows will always be in groups of four, you can

  • Add an index column where each group of rows has an assigned number
    • Without this unique identifier for each block, the Pivot with no aggregation will error
  • Pivot on Column 1 with Column2 values and no aggregation

 

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

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),

    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Column1]), "Column1", "Column2"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

 

The above code assumes each block has four rows.  If the numbers of rows might vary, but, for example, the first row is always "Name", then you can still assign a unique number to each block with the code below, and then do your pivot as before.

 

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Column1] = "Name" then [Index] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"Custom"}),

 

 

Fantastic! I was able to replicate these steps and achieve the result.

Huge thanks!

ronrsnfld
Community Champion
Community Champion

Are there always going to be four rows in a block? 

yes, it is.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors