Power Query - Extract data from one Column To Create Other Columns

[JPEliz_Sx]

Hey all! The attached image is a piece of the data set, there are 511 rows. I would like to remove the duplicates in column C, but before doing that I have to extract the data from column "E" and turn that into three separate columns (F, G, H).

For instance C1:C3 are all 17. A1:A3, B1:B3, D1:D3 are all the same values that correspond to C1:C3. However, E1:E3 has three distinct values that correlate to C1:C3 (52015, 54013, and 55014). I would like to add three columns call them First Match, Second Match, Third Match. These columns would list each of these three numbers separately and line up with the row where column C1 = 17. Can this be done in Power Query?

Any help would be much appreciated. Sorry if my post is confusing, please feel free to ask questions to clarify. Thanks in advance!

 

[Alex Blakenburg]

Is this what you had in mind ?

20211124 PQ Ungroup.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Year	Grp	Value	Acct			Date	Year	Grp	Value	First Match	Second Match	Third Match
2	14/07/2020	2020	17		52015			14/07/2020	2020	17		52015	54013	55014
3	14/07/2020	2020	17		54013			14/07/2020	2020	18		51015	53018	56013
4	14/07/2020	2020	17		55014			14/07/2020	2020	19		92006	93015	94012
5	14/07/2020	2020	18		51015			14/07/2020	2020	20		61032	63029	81033
6	14/07/2020	2020	18		53018			14/07/2020	2020	21	1308	64030	72034	74032
7	14/07/2020	2020	18		56013			14/07/2020	2020	22		62032	71032	93032
8	14/07/2020	2020	19		92006			14/07/2020	2020	23	1314	62031	73031	82034
9	14/07/2020	2020	19		93015
10	14/07/2020	2020	19		94012
11	14/07/2020	2020	20		61032
12	14/07/2020	2020	20		63029
13	14/07/2020	2020	20		81033
14	14/07/2020	2020	21	1308	64030
15	14/07/2020	2020	21	1308	72034
16	14/07/2020	2020	21	1308	74032
17	14/07/2020	2020	22		62032
18	14/07/2020	2020	22		71032
19	14/07/2020	2020	22		93032
20	14/07/2020	2020	23	1314	62031
21	14/07/2020	2020	23	1314	73031
22	14/07/2020	2020	23	1314	82034
23
Sheet1

If it is, this is the Power Query M code I used.
let
Source = Excel.CurrentWorkbook(){[Name="tblData"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Modulo",{"Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Modulo", type text}}, "en-AU"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Modulo", type text}}, "en-AU")[Modulo]), "Modulo", "Acct", List.Sum),
#"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type date}, {"Year", Int64.Type}, {"Grp", Int64.Type}, {"Value", Int64.Type}, {"0", Int64.Type}, {"1", Int64.Type}, {"2", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"0", "First Match"}, {"1", "Second Match"}, {"2", "Third Match"}})
in
#"Renamed Columns"