Transpose a table using power query

Question

I have the following table (as result of many steps) :

I want to get a result as below : as I need to transpose the table :

Transform ribbon > Transpose command. Explain why that will not work for you. — teylyn
@teylyn I tried to use pivot it but in vain. I am getting this error : "There were too many elements in the enumeration to complete the operation" — Amira Bedhiafi
I never mentioned pivot. In Power Query, there is the Transpose command. Is that not working for you? Come on, you should know how to ask a question. Please provide sufficient details so we can reproduce and help troubleshoot. — teylyn
@SandraGuilepZouaouiZandeh I don’t see any in the screenshots you have in the question, but did you check to see whether any of the rows have same values that would eventually get pivoted into columns? Duplicated rows that are to be pivoted to columns sometimes lead to this error. — Benson Igarabuza
Do you have per country record? If yes, show data for at least 2 country and provide the sample data in a excel file, so that people can easily copy your data for testing. — mkRabbani

Michal Palko Michal Palko · Accepted Answer · 2021-01-03T18:18:33

This will do. In your case NumberOfColumns is at least 19.

For explanation of essential line

 ToTable = Table.FromRows( List.Split( Source[Column.1.2], NumberOfColumns) )

visit The BI Accountant

let
      NumberOfColumns = 3
    , Source = Table.FromRecords({
          [Column.1 = "country", Column.1.2 = "afghanistan"],
          [Column.1 = "capital", Column.1.2 = "kabul"],
          [Column.1 = "currency", Column.1.2 = "afghani"],
          [Column.1 = "country", Column.1.2 = "slovakia"],
          [Column.1 = "capital", Column.1.2 = "bratislava"],
          [Column.1 = "currency", Column.1.2 = "eur"]
                })
    , ToTable = Table.FromRows( List.Split( Source[Column.1.2], NumberOfColumns) )
    , OldHeader = Table.ColumnNames(ToTable)
    , NewHeader = List.FirstN(Source[Column.1],NumberOfColumns)
    , RenameHeaders = Table.RenameColumns(ToTable, List.Zip({OldHeader, NewHeader}))
in
    RenameHeaders

before:

after:

Transpose a table using power query

1 Answers