PQ question - "splitting" x2 rows into 2 columns and then removing 2nd row

[scott_86_]

Hi,

Please see mini-sheet below. Could anyone please be able to assist in some applied steps (or M code) I can use to further transform the green table to the orange table?

// M-Code from Table 1 to Table1_1 in the below mini-sheet

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"User ID", Int64.Type}, {"Identifier", type text}, {"Version", Int64.Type}, {"Current status", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Name", "User ID", "Version", "Current status"})
in
    #"Filled Down"

000. Admin.xlsx
	A	B	C	D	E	F	G
1	INITIAL REPORT EXAMPLE		Blue Table Name = Table1
2	Name	User ID	Identifier	Version	Current status
3	Test, Name	9999	Admin-Test (Department)	88	Test status
4			Apprentice Mechanical (Position)
5
6
7	REPORT AFTER PQ FILL DOWN COLUMN A, B, D & E		Green Table Name = Table1_1
8	Name	User ID	Identifier	Version	Current status
9	Test, Name	9999	Admin-Test (Department)	88	Test status
10	Test, Name	9999	Apprentice Mechanical (Position)	88	Test status
11
12
13	FURTHER RESULT REQUIRED FOR Table1_1 (as below)
14	- Create new column "Identifer2"
15	- Populate "Identifier2" with value in "Identifer" Row 2 (purple value)
16	- Remove Row 2
17	- Repeat for 'x' amount of names in actual report
18
19	Name	User ID	Identifier	Identifier2	Version	Current status
20	Test, Name	9999	Admin-Test (Department)	Apprentice Mechanical (Position)	88	Test status
21
22
PQ Ques

 

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = List.Split(Table.ToList(Source, each _), 2), 
    tbl = Table.FromList(List.Transform(lst, (x) => x{0} & {x{1}{2}}), each _, Table.ColumnNames(Source) & {"Identifier2"}), 
    reorder = Table.ReorderColumns(tbl, {"Identifier2", "Version", "Current status"})
in
    reorder