Data Cleaning: All data in rows > Convert to a proper table

[goko]

Hi,
I want to solve this in Power Query

I have the following data.

Book1
	D
8	Sr. no.
9	Product
10	Qty
11	Quality
12	1
13	apple
14	99
15	average
16	2
17	oranges
18	42
19	good
20	3
21	banana
22	10
23	low
Sheet1

I want to transform this into a proper table

Book1
	G	H	I	J
7	Sr. no.	Product	Qty	Quality
8	1	apple	99	average
9	2	oranges	42	good
10	3	banana	10	low
Sheet1

Thanks

 

[RoryA]

Do you have a header in D7?

 

[goko]

Do you have a header in D7?

No.

D8 till D11 are the headers and the subsequent data is in rows.

I tried to play around with index columns and adding +4 to index but that got me nowhere

 

[RoryA]

Assuming D8 onwards is a table called Table1, you could use:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundDown(([Index]-1)/4)+1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Number.Mod([Index],4)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.1", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.1", type text}}, "en-US")[Custom.1]), "Custom.1", "Column1"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sr. no.", Int64.Type}, {"Product", type text}, {"Qty", Int64.Type}, {"Quality", type text}})
in
    #"Changed Type1"

 

[goko]

Assuming D8 onwards is a table called Table1, you could use:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundDown(([Index]-1)/4)+1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Number.Mod([Index],4)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.1", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Custom.1", type text}}, "en-US")[Custom.1]), "Custom.1", "Column1"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Sr. no.", Int64.Type}, {"Product", type text}, {"Qty", Int64.Type}, {"Quality", type text}})
in
    #"Changed Type1"

Thank you very much

 

[JGordon11]

Another way:

let
    lst = Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Column1],
    ColCt = 4,
    Headers = List.FirstN(lst,ColCt),
    Rows = List.Generate(()=>ColCt, (x)=> x < List.Count(lst), (x)=>x+ColCt, (x)=> List.Range(lst,x,ColCt)),
    Result= Table.FromRows(Rows,Headers)
in
    Result

Book1
	A	B	C	D	E	F	G
1	Column1		Sr. no.	Product	Qty	Quality
2	Sr. no.		1	apple	99	average
3	Product		2	oranges	42	good
4	Qty		3	banana	10	low
5	Quality
6	1
7	apple
8	99
9	average
10	2
11	oranges
12	42
13	good
14	3
15	banana
16	10
17	low
18
Sheet1