Forum Discussion

Isidrolj's avatar
Isidrolj
New Member
5 years ago
Solved

Create row categories on uncategorized rows and change rows to columns

Hello, 

 

I have a table with 1,000 rows and one column.

This is a copy-paste from another data source that had three columns (display_name, name, type)

I want to divide a thousand rows into those columns (display_name, name, type).

The rows have not been marked, indexed or grouped.

Can you help me with how to do it with PowerQuery. 

 

See an example below: 

 

Raw data: 

 

Header1|

a

b

c

d

e

f

g

h

i

j

k

l

 

Desired output: 

 

display_name | name | type |

a                       b            c

d                       e            f

g                       h            i

j                         k            l

 

 

Thanks! 

 

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Isidrolj 

    Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcS3DQAwEAOxXVR7o4cK57h/beBYMEJZTqHClRt3Hjx58ebDl5/sDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Header = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Header", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each let n = Number.Mod(_, 3) in if n=0 then "Display Name" else if n=1 then "Name" else if n=2 then "Type" else null, type text}}),
    #"Added Index1" = Table.AddIndexColumn(#"Calculated Modulo", "Index.1", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index1", {{"Index.1", each Number.IntegerDivide(_, 3), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Index]), "Index", "Header"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index.1"})
in
    #"Removed Columns"

     



3 Replies

  • Jakinta's avatar
    Jakinta
    Icon for Solution Sage rankSolution Sage
    5 years ago

     

    = Table.FromRows( List.Transform( List.Split( PriorStepName[Header1], 3) , each _& List.Repeat({""}, 3-List.Count(_)) ), { "display_name", "name", "type"} )

     

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Isidrolj 

    Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcS3DQAwEAOxXVR7o4cK57h/beBYMEJZTqHClRt3Hjx58ebDl5/sDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Header = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Header", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each let n = Number.Mod(_, 3) in if n=0 then "Display Name" else if n=1 then "Name" else if n=2 then "Type" else null, type text}}),
    #"Added Index1" = Table.AddIndexColumn(#"Calculated Modulo", "Index.1", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index1", {{"Index.1", each Number.IntegerDivide(_, 3), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Index]), "Index", "Header"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index.1"})
in
    #"Removed Columns"

     



    • Isidrolj's avatar
      Isidrolj
      New Member
      5 years ago

      Great! Thanks for the quick answer

       

      I found a solution, and I am happy we get similar results. 

       

      I used the below code: 

       

      let
      Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
      #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
      #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
      #"Added Conditional Column" = Table.AddColumn(#"Inserted Modulo", "display_name", each if [Modulo] = 1 then [Column1] else null),
      #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "name", each if [Modulo] = 2 then [Column1] else null),
      #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "type", each if [Modulo] = 0 then [Column1] else null),
      #"Filled Up" = Table.FillUp(#"Added Conditional Column2",{"name", "type"}),
      #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([display_name] <> null))
      in
      #"Filtered Rows"

       

      Kudos to @computergaga to post this useful youtube video https://www.youtube.com/watch?v=3rCDvZG_gTk