Table.Pivot - without aggreagation but to list values

[jlauyfc1]

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!!

[Omid_Motamedise · Accepted solution]

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]

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]

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

[tackytechtom]

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/