Power Query : Split Column Based On 'Folder & Files' Then Move Up The Same Row

[muhammad susanto]

hi all..

how to split column based on name "Folder" and "Files" ,
my expected start col C3 and D3 down
here this layout

Extract List Doc WBBM.xlsx
	A	B	C	D
1	original data		desired result
2	File's Name	Model	Col1 (folder)	Col2 (files)
3	Manajement	Folder	Manajement
4	1. Working Team	Folder	1. Working Team
5	1a	Folder	1a	Kep-11
6	Kep-11	Files		Kep-123
7	Kep-123	Files
8	1b	Folder	1b	attendance
9	attendance	Files		photos1
10	photos1	Files		document1
11	document1	Files		document2
12	document2	Files
13	2. Long Planning	Folder	2. Long Planning
14	5a Consument	Folder	5a Consument
15	5b	Folder	5b
16	5c	Folder	5c
17	5d	Folder	5d
18	5e	Folder	5e
Sheet1

anybody help out, thank in advance
susant

 

[GraH]

You can try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File's Name", type text}, {"Model", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Model]), "Model", "File's Name"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

 

[muhammad susanto]

You can try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File's Name", type text}, {"Model", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Model]), "Model", "File's Name"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

View attachment 79099

hi GraH...thanks but not fully work, please see my attachment picture should be the result is the move up and the same line