Forum Discussion

profindir's avatar
profindir
New Member
9 years ago
Solved

Transpose table

Can you help me to transpose this table

to that

in Power BI Desktop.

 

Thank you.

 

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    An alternative solution with far less code can be achieved with more advanced coding, using Table.Partition.

     

    let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    Partitioned = Table.Partition(#"Added Index","Index",3, each _),
    TableFromColumns = Table.FromColumns(
                            {Partitioned{0}[Column2],
                             Partitioned{0}[Column1],
                             Partitioned{1}[Column1],
                             Partitioned{2}[Column1]}),
    #"Promoted Headers" = Table.PromoteHeaders(TableFromColumns, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",
                           {{"Invoice Nº", type text},
                            {"Period", type date},
                            {"Sum", type number},
                            {"Quantity", Int64.Type}})
in
    #"Changed Type"

    Table.Partition creates a list with separate tables, of which the first table contains all data from the 1st, 4th, 7th etc. row, the second table from the 2nd, 5th, 8th etc row, the third table from the 3rd, 6th, 9th etc. row.

     

    Now the appropriate columns can be combined with Table.FromColumns.

5 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    Please share a picture from your table after import in Power BI (Query Editor).

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      Edit: refer to the next post for a far more compact, but more advanced solution.

       

      My suggestion is to first create separate tables for invoiceNo and for the other columns, both with an index column.

      As final result, merge the 2 tables.

      Of course, this is to be done in Power Query.

      All steps in the queries below are generated via standard menu-options. The pivot step in query OtherColumns was created with advanced option "Don't aggregate".

       

      Query InvoiceNo:

      let
    Source = Table1,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Column2] <> null and [Column2] <> ""),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Invoice Nº", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1)
in
    #"Added Index"

       

      Query OtherColumns:

      let
    Source = Table1,
    #"Removed Columns2" = Table.RemoveColumns(Source,{"Column2"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns2", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 3), Int64.Type}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Integer-Divided Column",{{"Index", type text}}),
    #"Added Index1" = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index1", {{"Index.1", each Number.Mod(_, 3), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Calculated Modulo", List.Distinct(#"Calculated Modulo"[Index]), "Index", "Column1"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index.1"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Added Index2" = Table.AddIndexColumn(#"Promoted Headers", "Index", 0, 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Index2",{{"Period", type date}, {"Sum", type number}, {"Quantity", Int64.Type}})
in
    #"Changed Type1"

      Query Result: 

      let
    Source = Table.NestedJoin(InvoiceNo,{"Index"},OtherColumns,{"Index"},"OtherColumns",JoinKind.LeftOuter),
    #"Expanded OtherColumns" = Table.ExpandTableColumn(Source, "OtherColumns", {"Period", "Sum", "Quantity"}, {"Period", "Sum", "Quantity"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded OtherColumns",{"Index"})
in
    #"Removed Columns"

       

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        An alternative solution with far less code can be achieved with more advanced coding, using Table.Partition.

         

        let
    Source = Table1,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    Partitioned = Table.Partition(#"Added Index","Index",3, each _),
    TableFromColumns = Table.FromColumns(
                            {Partitioned{0}[Column2],
                             Partitioned{0}[Column1],
                             Partitioned{1}[Column1],
                             Partitioned{2}[Column1]}),
    #"Promoted Headers" = Table.PromoteHeaders(TableFromColumns, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",
                           {{"Invoice Nº", type text},
                            {"Period", type date},
                            {"Sum", type number},
                            {"Quantity", Int64.Type}})
in
    #"Changed Type"

        Table.Partition creates a list with separate tables, of which the first table contains all data from the 1st, 4th, 7th etc. row, the second table from the 2nd, 5th, 8th etc row, the third table from the 3rd, 6th, 9th etc. row.

         

        Now the appropriate columns can be combined with Table.FromColumns.