Can Power Query solve this problem for me?

[Baconzilla]

I tried searching for the answer, but the problem is that I don't even know how to phrase the question for a search. I receive a csv file with data broken out into different groups of rows and columns. I want to clean up all of the data into one table. I tried highlighting the examples below to make it easier to follow:

I start with a csv file that contains this:
Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Company	901
2	Scenario
3	Year	2018
4	Stat Code
5
6	Account	Acct Descr	Dept	Prod	Proj	January	February	March	April	May	June	July	August	September	October	November	December
7	10001		25			2,905	3,149	2,600	2,666	3,553	2,822	3,907	4,018	3,532	2,879	3,337	3,716
8	10002		25			59	26	1,524	779	1,371	2,432	3,200	2,683	1,958	2,759	2,449	1,934
9	10003		25			3	3	4	4	4	4	5	4	5	4	5	4
10	10004		25			20	11	289	202	367	693	690	547	407	520	615	647
11
12	Company	902
13	Scenario
14	Year	2018
15	Stat Code
16
17	Account	Acct Descr	Dept	Prod	Proj	January	February	March	April	May	June	July	August	September	October	November	December
18	10001		25			3,782	3,710	3,954	3,794	3,952	3,364	3,812	3,103	3,927	4,092	2,871	3,205
19	10002		25			578	723	2,213	1,500	1,431	2,940	3,390	2,914	1,849	2,448	2,534	2,396
20	10003		25			4	4	5	6	4	6	6	4	6	5	4	3
21	10004		25			122	232	415	212	355	707	678	726	611	544	606	605
22
23	Company	903
24	Scenario
25	Year	2018
26	Stat Code
27
28	Account	Acct Descr	Dept	Prod	Proj	January	February	March	April	May	June	July	August	September	October	November	December
29	10001		25			9,812	8,039	10,244	9,737	8,458	8,816	9,476	11,160	8,820	10,044	10,620	10,974
30	10002		25			873	1,080	3,608	4,338	5,397	9,479	11,195	7,887	6,143	7,210	6,639	5,403
31	10003		25			6	6	5	5	6	6	5	4	4	5	4	4
32	10004		25			189	204	897	682	1,113	1,856	1,657	1,448	1,577	2,001	1,752	1,472

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sample Data​

I want the end result to look like this:
Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Company	Year	Account	Dept	January	February	March	April	May	June	July	August	September	October	November	December
2	901	2018	10001	25	2,905	3,149	2,600	2,666	3,553	2,822	3,907	4,018	3,532	2,879	3,337	3,716
3	901	2018	10002	25	59	26	1,524	779	1,371	2,432	3,200	2,683	1,958	2,759	2,449	1,934
4	901	2018	10003	25	3	3	4	4	4	4	5	4	5	4	5	4
5	901	2018	10004	25	20	11	289	202	367	693	690	547	407	520	615	647
6	902	2018	10001	25	3,782	3,710	3,954	3,794	3,952	3,364	3,812	3,103	3,927	4,092	2,871	3,205
7	902	2018	10002	25	578	723	2,213	1,500	1,431	2,940	3,390	2,914	1,849	2,448	2,534	2,396
8	902	2018	10003	25	4	4	5	6	4	6	6	4	6	5	4	3
9	902	2018	10004	25	122	232	415	212	355	707	678	726	611	544	606	605
10	903	2018	10001	25	9,812	8,039	10,244	9,737	8,458	8,816	9,476	11,160	8,820	10,044	10,620	10,974
11	903	2018	10002	25	873	1,080	3,608	4,338	5,397	9,479	11,195	7,887	6,143	7,210	6,639	5,403
12	903	2018	10003	25	6	6	5	5	6	6	5	4	4	5	4	4
13	903	2018	10004	25	189	204	897	682	1,113	1,856	1,657	1,448	1,577	2,001	1,752	1,472

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Intended Result​

The actual csv file has hundreds of these datasets. Can Power Query do this? If so, what tools/features should I read up on in order to do this?

Thank you!

 

[Ron Coderre]

After selecting the data range an connecting it to Power Query...These are the steps I took:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Company", each if [Column1] = "Company" then [Column2] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Company"}),
    #"Added Conditional Column1" = Table.AddColumn(#"Filled Down", "Year", each if [Column1] = "Year" then [Column2] else null),
    #"Filled Down1" = Table.FillDown(#"Added Conditional Column1",{"Year"}),
    #"Kept First Rows" = Table.FirstN(#"Filled Down1",4),
    #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Column1"}),
    #"Merged Queries" = Table.NestedJoin(#"Filled Down1",{"Column1"},#"Removed Other Columns",{"Column1"},"Removed Other Columns",JoinKind.LeftAnti),
    #"Filtered Rows" = Table.SelectRows(#"Merged Queries", each [Column1] <> null),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column2", "Column4", "Column5", "Removed Other Columns"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Account", type any}, {"Dept", type any}, {"January", type any}, {"February", type any}, {"March", type any}, {"April", type any}, {"May", type any}, {"June", type any}, {"July", type any}, {"August", type any}, {"September", type any}, {"October", type any}, {"November", type any}, {"December", type any}, {"Column15", type any}, {"901", Int64.Type}, {"2018", Int64.Type}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each [Account] <> "Account"),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"901", "2018", "Account", "Dept", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December", "Column15"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"901", "Company"}})
in
    #"Renamed Columns"

Is that something you can work with?

 

[Baconzilla]

Thank you! I will try this as soon as I get a chance!

 

[sandy666]

or

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] = "Company" then [Column1] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Column1] = "Company" then [Column2] else null),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Custom.2", each if [Column1] = "Year" then "Year" else null),
    #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Custom.3", each if [Column1] = "Year" then [Column2] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column3",{"Custom", "Custom.1", "Custom.2", "Custom.3"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"Custom", "Custom.1", "Custom.2", "Custom.3"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Column1] = 10001 or [Column1] = 10002 or [Column1] = 10003 or [Column1] = 10004 or [Column1] = "Account")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Account", type any}, {"Acct Descr", type text}, {"Dept", type any}, {"Prod", type text}, {"Proj", type text}, {"January", type any}, {"February", type any}, {"March", type any}, {"April", type any}, {"May", type any}, {"June", type any}, {"July", type any}, {"August", type any}, {"September", type any}, {"October", type any}, {"November", type any}, {"December", type any}, {"Company", type text}, {"901", Int64.Type}, {"Year", type text}, {"2018", Int64.Type}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([Account] <> "Account")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows1", "Index", 1, 1),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Company]), "Company", "901"),
    #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Year]), "Year", "2018"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column1",{"Company", "Year", "Account", "Acct Descr", "Dept", "Prod", "Proj", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Acct Descr", "Prod", "Proj", "Index"})
in
    #"Removed Columns"[/SIZE]

example file

 

[billszysz]

If your data is as regular as shown in the sample then this code below should also work.

let
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 11), Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Index"}, {{"MainTbl", (x) => Table.AddColumn(Table.PromoteHeaders(Table.RemoveColumns(Table.RemoveFirstN(x, 5), "Index")), "Rec", each [Company = x{0}[Column2], Year = x{2}[Column2]] ), type table}}),
    Combine = Table.Combine(#"Grouped Rows"[MainTbl]),
    #"Reordered Columns" = Table.ReorderColumns(Combine, List.LastN(Table.ColumnNames(Combine), 1) & List.RemoveLastN(Table.ColumnNames(Combine), 1)  ),
    #"Expanded {0}" = Table.ExpandRecordColumn(#"Reordered Columns", "Rec", {"Company", "Year"}, {"Company", "Year"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded {0}", each ([Account] <> null))
in
    #"Filtered Rows"

You can download an example file from the link below

https://drive.google.com/open?id=1WQSXdjmltO3dINp2zqIl-k1SLqTWD8kF