power query filter...

[Urraco]

Hello everybody,

I combined 4 xlsx files with powerQ.
How could I generate a new filtered list like in the example on the right table ?
Thank you very much.

test.xlsx
	A	B	C	D	E	F	G	H	I
1	filename	name	Count		name	file1.xlsx	file2.xlsx	file3.xlsx	file4.xlsx
2	file1.xlsx	Adrain	2		Adrain	2	1		4
3	file1.xlsx	Michael	4		Alexandra	7	29		29
4	file1.xlsx	Alexandra	7		Ana		2		5
5	file1.xlsx	Theodor	5
6	file1.xlsx	Coleman	3
7	file1.xlsx	Babriel	8
8	file1.xlsx	Robert	5
9	file1.xlsx	Black	4
10	file2.xlsx	Paul	8
11	file2.xlsx	Black	11
12	file2.xlsx	Coleman	7
13	file2.xlsx	Adrain	1
14	file2.xlsx	Terry	6
15	file2.xlsx	Michael	8
16	file2.xlsx	Gal	8
17	file2.xlsx	Dragon	12
18	file2.xlsx	Alexandra	29
19	file2.xlsx	Flowers	4
20	file2.xlsx	Greg	6
21	file2.xlsx	Ana	2
22	file2.xlsx	Andreea	4
23	file3.xlsx	Dennis	13
24	file3.xlsx	Dean	3
25	file3.xlsx	Ned	4
26	file3.xlsx	Coleman	2
27	file3.xlsx	Peter	6
28	file3.xlsx	Theodor	17
29	file4.xlsx	Paul	9
30	file4.xlsx	Black	12
31	file4.xlsx	Coleman	7
32	file4.xlsx	Babriel	8
33	file4.xlsx	Robert	5
34	file4.xlsx	Ned	10
35	file4.xlsx	Terry	1
36	file4.xlsx	Adrain	4
37	file4.xlsx	Michael	4
38	file4.xlsx	Alexandra	29
39	file4.xlsx	Ana	5
40	file4.xlsx	Steven	2
41	file4.xlsx	Gal	2
42	file4.xlsx	Dragon	10
43	file4.xlsx	Flowers	4
44	file4.xlsx	Park	1
45	file4.xlsx	Greg	4
46	file4.xlsx	Andreea	2
47	file4.xlsx	Matei	2
Sheet2

 

[smozgur]

Select the filename column in the Power Query editor.
Select the Transform ribbon tab.
Click the Pivot Column and select the Count column name as the Values column (the column name in your data structure).
Click Ok.

The M Code will be something like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    PivotedColumn = Table.Pivot(Source, List.Distinct(Source[filename]), "filename", "Count", List.Sum)
in
    PivotedColumn

And the result:

name	file1.xlsx	file2.xlsx	file3.xlsx	file4.xlsx
Adrain	2	1		4
Alexandra	7	29		29
Ana		2		5
Andreea		4		2
Babriel	8			8
Black	4	11		12
Coleman	3	7	2	7
Dean			3
Dennis			13
Dragon		12		10
Flowers		4		4
Gal		8		2
Greg		6		4
Matei				2
Michael	4	8		4
Ned			4	10
Park				1
Paul		8		9
Peter			6
Robert	5			5
Steven				2
Terry		6		1
Theodor	5		17

 

[Urraco]

nice
Thank you!

Select the filename column in the Power Query editor.
Select the Transform ribbon tab.
Click the Pivot Column and select the Count column name as the Values column (the column name in your data structure).
Click Ok.

 

[smozgur]

nice
Thank you!

Glad to hear it helps!