Transpose from columns to rows with Power Query

Use this M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"PART 1", Int64.Type}, {"PART 2", Int64.Type}, {"PART 3", Int64.Type}, {"PART 4", Int64.Type}, {"PART 5", Int64.Type}, {"PART 6", Int64.Type}, {"PART 7", Int64.Type}, {"PART 8", Int64.Type}, {"PART 9", Int64.Type}, {"PART 10", Int64.Type}, {"PART 11", Int64.Type}, {"PART 12", Int64.Type}, {"PART 13", Int64.Type}, {"PART 14", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"DATE"}, "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",{"Attribute", "DATE", "Value"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Reordered Columns", {{"DATE", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Reordered Columns", {{"DATE", type text}}, "en-GB")[DATE]), "DATE", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Pivoted Column", "Attribute", "Attribute - Copy"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"Attribute", each Text.AfterDelimiter(_, " "), type text}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Extracted Text After Delimiter",{"Attribute - Copy", "Attribute", "01/01/2020", "02/01/2020", "03/01/2020", "04/01/2020", "05/01/2020", "06/01/2020", "07/01/2020", "08/01/2020", "09/01/2020", "10/01/2020", "11/01/2020", "12/01/2020", "13/01/2020", "14/01/2020", "15/01/2020", "16/01/2020", "17/01/2020", "18/01/2020", "19/01/2020", "20/01/2020", "21/01/2020", "22/01/2020", "23/01/2020", "24/01/2020", "25/01/2020"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Attribute", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Attribute", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute - Copy", "Part"}})
in
    #"Renamed Columns"

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Dear Ali,

In my original table i have a lot of different names as headers, for example 088888 659 Q.
When i try to adapt your code into my original table i am getting the following error:

DataFormat.Error: We couldn't convert to Number.
Details:
    659 Q

If i remove that column, i am getting the same error for another column, for example 075426 123 M

Any ideea why is this happening?

EDIT1:
I tried to change Int64.Type to Text.Type, just for fun...
I don't have any error now, but it only imports 92 row, instead of 177...

EDIT2:
i just realized that part of my table is not finished yet, so it contains no data yet. So i replace null with 0 in PQ and now i think that it imports all rows.
I must see how can i change the code, in order to not sort column a from smallest to greatest.

When you have time, please let me know if the changes i made are ok or are just silly

I can’t say without seeing more realistic data in a sample.

You have the Attribute Column as a Type Whole Number. It can't be and therefore you are getting a sort error. The column needs to be type "Text"

Here is the revised Mcode making that column Text.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"B00 000 01 01", Int64.Type}, {"B00 000 01 02", Int64.Type}, {"B00 000 01 03", Int64.Type}, {"B00 000 01 04", Int64.Type}, {"B00 000 01 05", Int64.Type}, {"B00 000 01 06", Int64.Type}, {"B00 000 01 07", Int64.Type}, {"B00 000 01 08", Int64.Type}, {"B00 000 01 09", Int64.Type}, {"B00 000 01 10", Int64.Type}, {"B00 000 01 11", Int64.Type}, {"B00 000 01 12", Int64.Type}, {"B00 000 01 13", Int64.Type}, {"B00 000 01 14", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"DATE"}, "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",{"Attribute", "DATE", "Value"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Reordered Columns", {{"DATE", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Reordered Columns", {{"DATE", type text}}, "en-GB")[DATE]), "DATE", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Pivoted Column", "Attribute", "Attribute - Copy"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"Attribute", each Text.AfterDelimiter(_, " "), type text}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Extracted Text After Delimiter",{"Attribute - Copy", "Attribute", "01/01/2020", "02/01/2020", "03/01/2020", "04/01/2020", "05/01/2020", "06/01/2020", "07/01/2020", "08/01/2020", "09/01/2020", "10/01/2020", "11/01/2020","12/01/2020", "13/01/2020", "14/01/2020", "15/01/2020", "16/01/2020", "17/01/2020", "18/01/2020", "19/01/2020", "20/01/2020", "21/01/2020", "22/01/2020", "23/01/2020", "24/01/2020", "25/01/2020"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Attribute", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Attribute", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute - Copy", "Part"}})
in
    #"Renamed Columns"

@Ali--Great piece of manipulation. Loved how you got it to sort by duplicating the column and removing the first character.

Thanks, Alan.