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!

 

[signup]

SEQ Pattern.xlsm
	A	B	C	D	E	F	G
1	SCENE NUMBER	CHARACTER NAME			CHARACTER NAME	Scenes	Count
2	E01 SC 01	MICHAEL			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	8
3	E01 SC 01	JACK			JACK	E01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 09	5
4	E01 SC 01	ELIZABETH			ELIZABETH	E01 SC 01, E01 SC 04, E02 SC 01	3
5	E01 SC 02	JOHN			JOHN	E01 SC 02, E02 SC 01, E02 SC 04, E02 SC 09	4
6	E01 SC 02	MICHAEL			SAMANTHA	E01 SC 03, E01 SC 04, E02 SC 02, E02 SC 08	4
7	E01 SC 03	JACK			LOGAN	E01 SC 03, E01 SC 04, E02 SC 03, E02 SC 07	4
8	E01 SC 03	SAMANTHA			EVAN	E02 SC 01	1
9	E01 SC 03	MICHAEL			ANGEL	E02 SC 03, E02 SC 07	2
10	E01 SC 03	LOGAN
11	E01 SC 04	ELIZABETH
12	E01 SC 04	SAMANTHA
13	E01 SC 04	LOGAN
14	E01 SC 05	MICHAEL
15	E01 SC 05	JACK
16	E02 SC 01	EVAN
17	E02 SC 01	ELIZABETH
18	E02 SC 01	JOHN
19	E02 SC 02	MICHAEL
20	E02 SC 02	SAMANTHA
21	E02 SC 03	ANGEL
22	E02 SC 03	LOGAN
23	E02 SC 04	JOHN
24	E02 SC 04	MICHAEL
25	E02 SC 05	JACK
26	E02 SC 06	MICHAEL
27	E02 SC 07	ANGEL
28	E02 SC 07	LOGAN
29	E02 SC 08	SAMANTHA
30	E02 SC 09	JACK
31	E02 SC 09	JOHN
32	E02 SC 10	MICHAEL
33	E02 SC 03	ANGEL
Sheet6

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"CHARACTER NAME"}, {{"Scenes", each Table.Distinct(_), type table [SCENE NUMBER=text, CHARACTER NAME=text]}, {"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
    Combine = Table.TransformColumns(Group,{{"Scenes", each Text.Combine(_[SCENE NUMBER],", ")}})
in
    Combine

Hey @lrobbo314
Can we get one more column, combining multiple values by grouping Character Name and using "," as a delimiter.?(Like we did for Scene Number Column)

Thanks in advance!

Book1
	A	B	C	D	E	F	G	H	I
1	SCENE NUMBER	CHARACTER NAME	COLUMN 2
2	E01 SC 01	MICHAEL	Text
3	E01 SC 01	JACK	Text			CHARACTER NAME	SCENES	TEXT
4	E01 SC 01	ELIZABETH	Text			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	Text, Text, Text
5	E01 SC 02	JOHN	Text			JACK	E01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 09	Text, Text, Text
6	E01 SC 02	MICHAEL	Text			ELIZABETH	E01 SC 01, E01 SC 04, E02 SC 01	Text, Text, Text
7	E01 SC 03	JACK	Text			JOHN	E01 SC 02, E02 SC 01, E02 SC 04, E02 SC 09	Text, Text, Text
8	E01 SC 03	SAMANTHA	Text			SAMANTHA	E01 SC 03, E01 SC 04, E02 SC 02, E02 SC 08	Text, Text, Text
9	E01 SC 03	MICHAEL	Text			LOGAN	E01 SC 03, E01 SC 04, E02 SC 03, E02 SC 07	Text, Text, Text
10	E01 SC 03	LOGAN	Text			EVAN	E02 SC 01	Text, Text, Text
11	E01 SC 04	ELIZABETH	Text			ANGEL	E02 SC 03, E02 SC 07	Text, Text, Text
12	E01 SC 04	SAMANTHA	Text
13	E01 SC 04	LOGAN	Text
14	E01 SC 05	MICHAEL	Text
15	E01 SC 05	JACK	Text
16	E02 SC 01	EVAN	Text
17	E02 SC 01	ELIZABETH	Text
18	E02 SC 01	JOHN	Text
19	E02 SC 02	MICHAEL	Text
20	E02 SC 02	SAMANTHA	Text
21	E02 SC 03	ANGEL	Text
22	E02 SC 03	LOGAN	Text
23	E02 SC 04	JOHN	Text
24	E02 SC 04	MICHAEL	Text
25	E02 SC 05	JACK	Text
26	E02 SC 06	MICHAEL	Text
27	E02 SC 07	ANGEL	Text
28	E02 SC 07	LOGAN	Text
29	E02 SC 08	SAMANTHA	Text
30	E02 SC 09	JACK	Text
31	E02 SC 09	JOHN	Text
32	E02 SC 10	MICHAEL	Text
Sheet1

 

[signup]

Hi, Can anyone help with this?

 

[lrobbo314]

In your example it says 'Text,Text,Text'. What do you actually want in that column?

 

[lrobbo314]

Do you mean like this?

Book2
	A	B	C	D	E	F	G	H
1	SCENE NUMBER	CHARACTER NAME	Test		CHARACTER NAME	Scenes	Test	Count
2	E01 SC 01	MICHAEL	A		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	A,C,B	8
3	E01 SC 01	JACK	A		JACK	E01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 09	A,C,B	5
4	E01 SC 01	ELIZABETH	C		ELIZABETH	E01 SC 01, E01 SC 04, E02 SC 01	C,A	3
5	E01 SC 02	JOHN	C		JOHN	E01 SC 02, E02 SC 01, E02 SC 04, E02 SC 09	C,B,A	4
6	E01 SC 02	MICHAEL	C		SAMANTHA	E01 SC 03, E01 SC 04, E02 SC 02, E02 SC 08	C,B	4
7	E01 SC 03	JACK	C		LOGAN	E01 SC 03, E01 SC 04, E02 SC 03, E02 SC 07	B,A	4
8	E01 SC 03	SAMANTHA	C		EVAN	E02 SC 01	C	1
9	E01 SC 03	MICHAEL	C		ANGEL	E02 SC 03, E02 SC 07, E02 SC 03	B,C,A	3
10	E01 SC 03	LOGAN	B
11	E01 SC 04	ELIZABETH	A
12	E01 SC 04	SAMANTHA	B
13	E01 SC 04	LOGAN	B
14	E01 SC 05	MICHAEL	C
15	E01 SC 05	JACK	B
16	E02 SC 01	EVAN	C
17	E02 SC 01	ELIZABETH	A
18	E02 SC 01	JOHN	B
19	E02 SC 02	MICHAEL	B
20	E02 SC 02	SAMANTHA	B
21	E02 SC 03	ANGEL	B
22	E02 SC 03	LOGAN	A
23	E02 SC 04	JOHN	A
24	E02 SC 04	MICHAEL	C
25	E02 SC 05	JACK	C
26	E02 SC 06	MICHAEL	C
27	E02 SC 07	ANGEL	C
28	E02 SC 07	LOGAN	A
29	E02 SC 08	SAMANTHA	C
30	E02 SC 09	JACK	B
31	E02 SC 09	JOHN	A
32	E02 SC 10	MICHAEL	A
33	E02 SC 03	ANGEL	A
Sheet1

 

[alansidman]

crossposted: Power Query - Add another column to Table.TransformColumns

 

[signup]

crossposted: Power Query - Add another column to Table.TransformColumns

Yeah, some random text like below.

SCENE NUMBER	CHARACTER NAME	COLUMN 2
E01 SC 01	MICHAEL	TEXT1, TEXT2
E01 SC 01	JACK	TEXT3
E01 SC 01	ELIZABETH	TEXT4, TWXT 5
E01 SC 02	JOHN	TEXT6, TEXT 7
E01 SC 02	MICHAEL	TEXT8,
E01 SC 03	JACK	TEXT9,
E01 SC 03	SAMANTHA	TEXT10, TEXT11
E01 SC 03	MICHAEL	TEXT12, TEXT13
E01 SC 03	LOGAN	TEXT15
E01 SC 04	ELIZABETH	TEXT16, TEXT17
E01 SC 04	SAMANTHA	TEXT18, TEXT19, TEXT20

 

[signup]

crossposted: Power Query - Add another column to Table.TransformColumns

Sorry to do this. I pinged @lrobbo314 about my work urgency. However, it's my mistake. I will avoid this crossposting in the future.
Thanks for helping with this.

 

[lrobbo314]

So does that solution work for what you want, or no?

 

[lrobbo314]

Please post some example data and expected results. It'll make this cluster f go along much more quickly.

 

[signup]

Please post some example data and expected results. It'll make this cluster f go along much more quickly.​

Just as an example..

​

Scene Number​	LOCATION​	Vehicles​
EP1 SC1​	VILLAGE ROADS​	Logan Ambulance​
EP1 SC2​	NATIONAL HIGHWAY​	Logan ambulance​
EP1 SC2​	NATIONAL HIGHWAY​	black SUV​
EP1 SC3​	FLIGHT​	flight​
EP1 SC4​	HIGHWAY ROAD​	Jack bike​
EP1 SC8​	BEACH​	Tatasumo for terrorist​
EP1 SC8​	BEACH​	Vani car​
EP1 SC8​	BEACH​	Old Car​
EP1 SC12A​	JACK'S HOUSE​	swift car​
EP1 SC13​	MICHAEL'S HOUSE COLONY ROAD​	Logan car​
EP1 SC13​	MICHAEL'S HOUSE COLONY ROAD​	Michael bike​
EP1 SC13​	MICHAEL'S HOUSE COLONY ROAD​	Accident vehicle scorpio​
EP1 SC13​	MICHAEL'S HOUSE COLONY ROAD​	Police vehicle​
EP1 SC13​	MICHAEL'S HOUSE COLONY ROAD​	extra car​
EP1 SC13​	MICHAEL'S HOUSE COLONY ROAD​	Bike 2​
EP1 SC14​	MAIN ROAD​	Michael's bike​
EP1 SC14​	MAIN ROAD​	two buses​
EP1 SC14​	MAIN ROAD​	cars 20​
EP1 SC14​	MAIN ROAD​	bikes and scooters 40​
EP1 SC14​	MAIN ROAD​	Autos 5​
EP1 SC14​	MAIN ROAD​	Tempo vans 2​
EP1 SC14​	MAIN ROAD​	John bike​
EP1 SC14​	MAIN ROAD​	Bharath bike​
EP1 SC14​	MAIN ROAD​	Foods cart​
EP1 SC14​	MAIN ROAD​	Jack bike​
EP1 SC14​	MAIN ROAD​	6689 car​
EP1 SC14​	MAIN ROAD​	Police vehicles 2​
EP1 SC14​	MAIN ROAD​	ambulance​
EP1 SC15​	U TURN POINT​	ambulance​
EP1 SC17​	JOHN'S CAR : ROAD​	John car​
EP1 SC17​	JOHN'S CAR : ROAD​	car​
EP1 SC17​	JOHN'S CAR : ROAD​	bikes​
EP1 SC18​	JOHN'S OFFICE​	John car​
EP1 SC29A​	ROAD UNKNOWN​	John bike​

Required output as Location-wise Vehicles. Also, If you can help with scene-wise vehicles in that location would be a great help. In shooting so couldn't respond back to you early.
Thanks!!