Forum Discussion

jlauyfc1's avatar
jlauyfc1
Regular Visitor
1 year 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 result:

X | Y

-----

a   c

b  d

    e

 

Code:

What i am thinking about is:

 

= Table.Pivot(#"Source", List.Distinct(#"Source"[#"B"]), "B", "A", each .....)

 

 

Thanks in advance!!

Need Help
  • Omid_Motamedise's avatar
    Omid_Motamedise
    1 year 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 

     

4 Replies

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    1 year ago

    Hi jlauyfc1 ,

     

    How about this:

     

    Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUYpQitWJVkqCs5KBrEgwKwXOSoWwYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"A", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"B"}, {{"Grouping", each _, type table [A=nullable text, B=nullable text]}}),
    #"Added Index" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn ( [Grouping], "Index", 1 )),
    #"Removed Columns 1" = Table.RemoveColumns(#"Added Index",{"B", "Grouping"}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Removed Columns 1", "Index", {"A", "B", "Index"}, {"A", "B","Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Index", List.Distinct(#"Expanded Index"[B]), "B", "A"),
    #"Removed Columns 2" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns 2"

     

     

    Let me know, if this solved your query.

     

    /Tom
    https://www.tackytech.blog/
    https://www.instagram.com/tackytechtom/

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Super User
    1 year 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 

     

    • jlauyfc1's avatar
      jlauyfc1
      Regular Visitor
      1 year ago

      This is nicer with shorter coding. I amended your last line of code to make it work on any number of columns:

      = Table.FromRows(List.Zip(Record.ToList(Table.First(Pivot))), Table.ColumnNames(Pivot))

       

      • Omid_Motamedise's avatar
        Omid_Motamedise
        Super User
        1 year ago

        Yes, it is dynamic and can work on any number, if you need more help or explanation just let me know