Forum Discussion

jlauyfc1's avatar
jlauyfc1
Regular Visitor
2 years ago
Solved

Table.Pivot - without aggreagation but to list values

I am new to power query, how can I pivot a table without aggreagation but showing a list of (distinct) value? For example:   Table: A | B ----- a   X b   X c   Y d   Y e   Y   Expected res...
Need Help
  • Omid_Motamedise's avatar
    Omid_Motamedise
    2 years ago

    use this formula

    let
    Query1 = Table.FromColumns({{"a".."e"},{"X","X","Y","Y","Y"}},{"A","B"}),
    Pivot = Table.Pivot(Query1, List.Distinct(Query1[B]), "B", "A", each _),
    Custom1 = Table.FromRows(List.Zip({Pivot[X]{0},Pivot[Y]{0}}),Table.ColumnNames(Pivot))
in
    Custom1

     

    it result in 

     

Omid_Motamedise's avatar
Omid_Motamedise
Icon for Super User rankSuper User
2 years ago

use this formula

let
    Query1 = Table.FromColumns({{"a".."e"},{"X","X","Y","Y","Y"}},{"A","B"}),
    Pivot = Table.Pivot(Query1, List.Distinct(Query1[B]), "B", "A", each _),
    Custom1 = Table.FromRows(List.Zip({Pivot[X]{0},Pivot[Y]{0}}),Table.ColumnNames(Pivot))
in
    Custom1

 

it result in