Transform a two column table to a tiled "list"

[Marky_Mark]

So, I've got a table that looks a little bit like this....

Employee Name	Manager Name
Joe	Bob
Bill	Mary
Stacy	Bob
Linda	Ed
Richard	Bob
Harry	Mary

However, I want to load this into an Excel sheet using Power Query into something that looks like this....

Bob	Mary	Ed
Joe	Bill	Linda
Stacy	Harry
Richard

Can anyone help?

[Anonymous]

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVdJRcspPUorViVZyyszJAXJ9E4sqwfzgksTkSiR5n8y8lEQg3zUFzA3KTM5ILEpBUuCRWFRUCTchFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Manager Name" = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"Employee Name", type text}, {"Manager Name", type text}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Manager Name"}, {{"empl", each [Employee Name]}}),
    tfc=Table.FromColumns(#"Raggruppate righe"[empl],#"Raggruppate righe"[Manager Name])
in
   tfc

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

TRANSPOSE APPROACH

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVdJRcspPUorViVZyyszJAXJ9E4sqwfzgksTkSiR5n8y8lEQg3zUFzA3KTM5ILEpBUuCRWFRUiWwCSAdYIDM7VSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Manager Name" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Manager Name"}, {{"String", each Text.Combine([Employee Name],","), type nullable text}}),
    Custom1 = Table.SplitColumn(#"Grouped Rows", "String", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), List.Transform({1..List.Max(Table.AddColumn(#"Grouped Rows", "Temp", each List.Count(Text.Split([String],",")))[Temp])},each "String." & Number.ToText(_))),
    #"Transposed Table" = Table.Transpose(Custom1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in
    #"Promoted Headers"

PIVOT APPROACH

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVdJRcspPUorViVZyyszJAXJ9E4sqwfzgksTkSiR5n8y8lEQg3zUFzA3KTM5ILEpBUuCRWFRUiWwCSAdYIDM7VSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Name" = _t, #"Manager Name" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Manager Name"}, {{"Count", each _, type table [Employee Name=nullable text, Manager Name=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Employee Name", "Index"}, {"Employee Name", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[#"Manager Name"]), "Manager Name", "Employee Name"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"