Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Beginner question : Table (Transpose and Pivot?)

Hello! I have a small problem. I have those data: My goal would be to put them in order: Like in yellow you can see my headers which are in the middle of the data and in different way (Sometim...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    I would first Split by Delimiter using the "/", and then split the table into a bunch of two row tables using Table.Split, with 2 as the parameter. This gives you a list of two row tables.

     

    Then I would Promote headers on the nested Tables, then reorder the columns in the nested tables.

     

    The promote headers and reorder steps are:

     

    Split1 = List.Transform(PriorStepName, each Table.PromoteHeaders(_))

    Reorder = List.Transform(Split1, each Table.ReorderColumns(_, {"ID, "Category", "Product", "Daily Goal"}))

     

    Now you can expand the list into a single table. 

    --Nate

ImkeF's avatar
ImkeF
Community Champion
4 years ago

Hi Anonymous ,
with a a little bit of math, you can do it using the UI alone:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZLBbsIwEER/BeVca504Ie6VuqW0ByrRE4iDG6xgNfVGsSPB39eYEpGE9tD7zNvZ2d1sogfpVInNEd4a3LWFAyF1dZzMUVawENH2bhM9y6pqC22wVMaCwPajUk4dIKEUZoRTzoNsIa69Q26QPJKMcwaxNy5rjXpnYeUaNOWnPgTBJUTn7kGDRMgv6xptSujnmhOe5SnEPlXQjRYbo/qAZa0ctsUeXkiW5gxGoP6C13sJVTfKWmncKcc0iSnkfsdVgW4i0Gj7k/zXdi5Vx6m39WgKbWgIXkmWpPEQdKOwM4id5oeetO3KnpGMxd21/jj9eQzhjLJBzWvESobb097FrnkDzlNrJu+NN/nxU5YOgIz/r2f/S/Q+Cb+0lm6vCoy2228=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column2", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Inserted Integer-Division", {{"Index", each Number.Mod(_, 2), type number}}),
    #"Added Custom1" = Table.AddColumn(#"Calculated Modulo", "Custom", each Text.Split([Column2], "/")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column2"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Added Index1" = Table.AddIndexColumn(#"Expanded Custom", "Index.1", 0, 1, Int64.Type),
    #"Calculated Modulo1" = Table.TransformColumns(#"Added Index1", {{"Index.1", each Number.Mod(_, 4), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Calculated Modulo1", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Calculated Modulo1", {{"Index", type text}}, "en-US")[Index]), "Index", "Custom", null),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index.1"}),
    #"Pivoted Column1" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"0"]), "0", "1", null)
in
    #"Pivoted Column1"
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I tried to go through your solution but it's maybe still a little bit complicated for my level of beginner, but it was very interesting to go through and try to understand every steps, thank you a lot !