Forum Discussion

GDE's avatar
GDE
Regular Visitor
6 years ago
Solved

Transpose a List in Power Query

How do I Transpose a List like:

  • {1, 2, 3}

into another List like:

  • {{1}, {2}, {3}}

in Power Query?

 

Thanks in advance!

Need Help
Tutorial Requests
  • mapelli's avatar
    mapelli
    3 years ago

    = List.Zip( { your_list } )

    example: List.Zip( { {1, 2, 3} } ) = { {1}, {2}, {3} } 

7 Replies

  • robrog37's avatar
    robrog37
    Regular Visitor
    6 years ago

    Table.ToRows(Table.FromList(List))

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    6 years ago

    GDE 

     

    let
    Source = {1, 2, 3},
    New= List.Generate(()=>[a={},b=0], each [b] <= List.Count(Source), each [a={Source{b}},b=[b]+1], each [a])
in
    New
    • GDE's avatar
      GDE
      Regular Visitor
      6 years ago

      Wow! That is a mighty function right there! A little (a lot) out of my leaugue...
      Respect!

      • AlB's avatar
        AlB
        Community Champion
        6 years ago

        GDE 

        another option:

        List.Transform( yourList_, each {_} )

        Please mark the question solved when done and consider giving kudos if posts are helpful.

        Contact me privately for support with any larger-scale BI needs, tutoring, etc.

        Cheers 

         

  • mapelli's avatar
    mapelli
    Frequent Visitor
    3 years ago

    = List.Zip( { your_list } )

    example: List.Zip( { {1, 2, 3} } ) = { {1}, {2}, {3} } 

    • GDE's avatar
      GDE
      Regular Visitor
      3 years ago

      IMHO, this is the best one, I've seen so far. 🙂

      • mapelli's avatar
        mapelli
        Frequent Visitor
        3 years ago

        Yeah... this is the equivalent to the transpose for lists.
        I use it often with Table.RenameColumns; example:

         

        let
        Source = Table.FromRecords({
        [name_1= "John", address_2= "CA", id_3= "[email protected]"],
        [name_1= "Juan", address_2= "MX", id_3= "[email protected]"],
        [name_1= "João", address_2= "BR", id_3= "[email protected]"]
        }),

        TransposedLists = List.Zip( { /* current column names: */ Table.ColumnNames(Source),
        /* new col names */ { "firstName", "country", "email" }
        } ),

        RenamedCols = Table.RenameColumns(Source, TransposedLists)
        in
        RenamedCols