Group By Character Name and list all Scene numbers

[signup]

Hello All, Hope you are doing Fine.

I have Excel data as shown in the below table. Can anyone help me to get the desired output?

SCENE NUMBER​	CHARACTER NAME​
E01 SC 01​	MICHAEL​
E01 SC 01​	JACK​
E01 SC 01​	ELIZABETH​
E01 SC 02​	JOHN​
E01 SC 02​	MICHAEL​
E01 SC 03​	JACK​
E01 SC 03​	SAMANTHA​
E01 SC 03​	MICHAEL​
E01 SC 03​	LOGAN​
E01 SC 04​	ELIZABETH​
E01 SC 04​	SAMANTHA​
E01 SC 04​	LOGAN​
E01 SC 05​	MICHAEL​
E01 SC 05​	JACK​
E02 SC 01​	EVAN​
E02 SC 01​	ELIZABETH​
E02 SC 01​	JOHN​
E02 SC 02​	MICHAEL​
E02 SC 02​	SAMANTHA​
E02 SC 03​	ANGEL​
E02 SC 03​	LOGAN​
E02 SC 04​	JOHN​
E02 SC 04​	MICHAEL​
E02 SC 05​	JACK​
E02 SC 06​	MICHAEL​
E02 SC 07​	ANGEL​
E02 SC 07​	LOGAN​
E02 SC 08​	SAMANTHA​
E02 SC 09​	JACK​
E02 SC 09​	JOHN​
E02 SC 10​	MICHAEL​

In the above sheet, I have character names from the scene. I need to prepare Character-wise scene numbers for my project. So, by making characters unique I need to create a list of scene numbers.

EXPECTED OUTPUT : (Note: Below output is Just to understand the requirement only but not precisely like this)

I need to get all the scene numbers list for the character.

MICHAEL - E01 SC 01, E01 SC 02, E01 SC 03, E01 SC 05, E02 SC 02, E02 SC 04, E02 SC 06, E02 SC 10
JACK - E01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 09
ELIZABETH - E01 SC 01, E01 SC 04, E02 SC 01
JOHN - E01 SC 02, E02 SC 01, E02 SC 04, E02 SC 09
SAMANTHA - E01 SC 03, E01 SC 04, E02 SC 02, E02 SC 08
LOGAN - E01 SC 03, E01 SC 04, E02 SC 03, E02 SC 07
EVAN - E02 SC 01
ANGEL - E02 SC 03, E02 SC 07

If there is any way to achieve this, Please help me.

Thanks in advance!

 

[lrobbo314]

FactSheet_Data_New (17).xlsx
	E	F	G
1	Location	Scene	Vehicles
2	VILLAGE ROADS	EP1 SC1	Logan Ambulance
3	NATIONAL HIGHWAY	EP1 SC2, EP1 SC2	Logan ambulance, black SUV
4	FLIGHT	EP1 SC3	flight
5	HIGHWAY ROAD	EP1 SC4	Jack bike
6	BEACH	EP1 SC8, EP1 SC8, EP1 SC8	Tatasumo for terrorist, Vani car, Old Car
7	JACK'S HOUSE	EP1 SC12A	swift car
8	MICHAEL'S HOUSE COLONY ROAD	EP1 SC13, EP1 SC13, EP1 SC13, EP1 SC13, EP1 SC13, EP1 SC13	Logan car, Michael bike, Accident vehicle scorpio, Police vehicle, extra car, Bike 2
9	MAIN ROAD	EP1 SC14, EP1 SC14, EP1 SC14, EP1 SC14, EP1 SC14, EP1 SC14, EP1 SC14, EP1 SC14, EP1 SC14, EP1 SC14, EP1 SC14, EP1 SC14, EP1 SC14	Michael's bike, two buses, cars 20, bikes and scooters 40, Autos 5, Tempo vans 2, John bike, Bharath bike, Foods cart, Jack bike, 6689 car, Police vehicles 2, ambulance
10	U TURN POINT	EP1 SC15	ambulance
11	JOHN'S CAR : ROAD	EP1 SC17, EP1 SC17, EP1 SC17	John car, car, bikes
12	JOHN'S OFFICE	EP1 SC18	John car
13	ROAD UNKNOWN	EP1 SC29A	John bike
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Location"}, {{"Scene", each _, type table [Scene Number=text, LOCATION=text, Vehicles=text]}}),
    Vehicles = Table.AddColumn(Group, "Vehicles", each Text.Combine([Scene][Vehicles],", ")),
    Scene = Table.TransformColumns(Vehicles,{{"Scene", each Text.Combine(_[Scene Number],", ")}})
in
    Scene