Creating a list from text data in spreadsheet

[ajbruff]

Hi

This has got me flummoxed and half the problem is I don't know what to search for for help.

I have a table which is similar to the one below.

Asset	Occupier
Anmer Hall	Prince William
Buckingham Palace	Queen Elizabeth
Balmoral	Queen Elizabeth
Birkhall	Prince Charles
Kensington Palace	Prince William
Highgrove	Prince Charles
Sandringham	Queen Elizabeth

I would like to get the data so I can see which occupiers have occupy which properties as shown below:

Queen Elizabeth	Prince Charles	Prince William
Buckingham Palace	Birkhall	Anmer Hall
Balmoral	Highgrove	Kensington Palace
Sandringham

Any advice on how to do this would be much appreciated.

Regards


Bruffster

 

[sandy666]

try Power Query (Get&Transform)

Asset	Occupier		Prince William	Queen Elizabeth	Prince Charles
Anmer Hall	Prince William		Anmer Hall	Buckingham Palace	Birkhall
Buckingham Palace	Queen Elizabeth		Kensington Palace	Balmoral	Highgrove
Balmoral	Queen Elizabeth			Sandringham
Birkhall	Prince Charles
Kensington Palace	Prince William
Highgrove	Prince Charles
Sandringham	Queen Elizabeth

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Occupier"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "List", each Table.Column([Count],"Asset")),
    Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    RC = Table.RemoveColumns(Extract,{"Count"}),
    Split = Table.SplitColumn(RC, "List", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"List.1", "List.2", "List.3"}),
    Transpose = Table.Transpose(Split),
    Promote = Table.PromoteHeaders(Transpose, [PromoteAllScalars=true])
in
    Promote

 

[ajbruff]

Hi Sandy

Thanks for your swift reply. I must admit, i'm not a techy so i'm not sure how to use the code. Can I get the result you got through the power query window?

Best wishes

Bruffster

 

[sandy666]

select your source table (blue)
Data - From Table
it will open PQ Editor
open Advanced Editor
check if the name there is the same as in the code from post (Table1)
if so replace whole code in Advanced Editor with code copied from the post
Done
Close&Load

 

[ajbruff]

AMAZING! Thank you so much!

 

[sandy666]

You are most welcome
Thanks for the feedback

btw. it works with your example, with another structure you'll need do these steps manually

 

[anvg]

Hi
A variant

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    prepare = Table.Group(Source, {"Occupier"}, {"temp", each [Asset]}),
    result = Table.FromColumns(prepare[temp], prepare[Occupier])
in
    result

Regards,

 

[ajbruff]

Thanks anvg, perfect!