Beginner question : Table (Transpose and Pivot?)

[Anonymous]

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 (Sometimes ID is first or second or third...). I would love to separe them correctly into one nice table.

What I tried, was to delimiter and then use the functions Pivot and Transpose to moove the data, but I couldn't achieve a good result. Could you give me a small tips on how I can do please?

Thank you very much, if you need I put my data in the link on the bottom (Table 3,4,5). I was already able to do it for Table 1,2,6.

https://wetransfer.com/downloads/7ef429368892372619e0a2bea4d5482320220219125527/a70e753875fcc8b77f8f...

[Anonymous]

Thanks to both of you! I'll try to understand both of your answers and comeback if I have any question!

Thank you a lot for your time.

[Anonymous · Accepted solution]

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

[Anonymous]

Thank you a lot ! I didn't know those formulas and they are very easy to understand and apply, I was able to understand very fast and use it everywhere I was stuck! Thank you

[ImkeF]

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]

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 !