Working with column subheadings in Excel

[Anonymous]

I have an Excel report that contains multiple column subheadings and I can't figure out how to transform to a simple table in Power Query. Here is an example of what the sheet looks like when it is first loaded into Power Query:

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11
department A	Application Name	Process A			Process B			Process C
department A		Status	RTO	RPO	Status	RTO	RPO	Status	RTO	RPO
department A	App A	Critical	4 hours	1 hour	Critical	4 hours	1 hour	Ancillary	12 hours	2 hours
department A	App B	Ancillary	72 hours	24 hours	Critical	1 hour	0 hours	Ancillary	5 days	24 hours
department A	App C	Critical	4 hours	1 hour	Ancillary	24 hours	4 hours	Critical	4 hours	1 hour
department B	Application Name	Process D			Process E			Process F
department B		Status	RTO	RPO	Status	RTO	RPO	Status	RTO	RPO
department B	App D	Critical	2 hours	0 hours	Ancillary	24 hours	4 hours	Critical	4 hours	1 hour
department B	App E	Critical	2 hours	0 hours	Ancillary	24 hours	4 hours	Ancillary	12 hours	12 hours
department B	App F	Ancillary	24 hours	12 hours	Critical	4 hours	1 hour	Critical	4 hours	1 hour

As you can see there are subheadings under each Process, and the report contains multiple tables like this with 2, 3, or 5 subheadings. How can I modify the table into a simpler format like the example below? I have 30 reports like this and am unable to manually modify the Excel sheets.

Department	Application	Process	Status	RTO	RPO
department A	App A	Process A	Critical	4 hours	1 hour
department A	App A	Process B	Critical	4 hours	1 hour
department A	App A	Process C	Ancillary	12 hours	2 hours
department A	App B	Process A	Ancillary	72 hours	24 hours
department A	App B	Process B	Critical	1 hour	0 hours
department A	App B	Process C	Ancillary	5 days	24 hours
department A	App C	Process A	Critical	4 hours	1 hour
department A	App C	Process B	Ancillary	24 hours	4 hours
department A	App C	Process C	Critical	4 hours	1 hour
department B	App D	Process D	Critical	2 hours	0 hours
department B	App D	Process E	Ancillary	24 hours	4 hours
department B	App D	Process F	Critical	4 hours	1 hour
department B	App E	Process D	Critical	2 hours	0 hours
department B	App E	Process E	Ancillary	24 hours	4 hours
department B	App E	Process F	Ancillary	12 hours	12 hours
department B	App F	Process D	Ancillary	24 hours	12 hours
department B	App F	Process E	Critical	4 hours	1 hour
department B	App F	Process F	Critical	4 hours	1 hour

[lbendlin]

If you can, modify your Excel table to bring it into a usable format.  If you cannot,  please post a sample Excel file and indicate the expected final format.

[Anonymous]

Unfortunately I cannot modify the Excel table. There are 30 different files all formatted the same, which are stored in the same folder and will be combined together. I am not able to upload a file due to IT restrictions, but I pasted before & after example tables in the OP

[lbendlin]

Will there always be three processes listed in a row or is that number flexible?

To give you an idea of the order of magnitude of that request - here is a subquery for  one ninth of the result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVM9D4IwFPwrDTODGI0zoIxC1I0wNEBik/KRUgb+vW1DS4VWgzJQXu7l3t2jR5o6RdlCQquypsB3XMdvW4xySFFTgyusSgYlpMnLrhNtMD4SCwxYqLDMXcznjTuFtO9YcXvE/EziFaBhJvMs3iFBlHnHrDyAZ9MTzvRE9bXr1znCGJKBg3vVl5VFNZgxTxpzEtGUld5OdXX+ERRweGNbhMNVC2lejK4W9Jlq8DkYZ0MILgYssgVDpmjLYIyehTlt1emCzDew0acS+/8ra8mkZwmllI7ss7Upv/8sWfYC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Rows", each _, type table }}),
    #"department A" = #"Grouped Rows"{[Column1="department A"]}[Rows],
    #"Removed Other Columns" = Table.SelectColumns(#"department A",{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Process", each #"Added Index"[Column3]{0}),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom"," ","Application Name",Replacer.ReplaceValue,{"Column2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Column1],each if [Index]=1 then "Department" else [Column1],Replacer.ReplaceValue,{"Column1"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",each [Process],each if [Index]=1 then "Process" else [Process],Replacer.ReplaceValue,{"Process"}),
    #"Removed Top Rows" = Table.Skip(#"Replaced Value2",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Removed Other Columns1" = Table.SelectColumns(#"Promoted Headers",{"Department","Application Name", "Process", "Status", "RTO", "RPO"})
in
    #"Removed Other Columns1"

This could then  be packed into a function and called nine times, and then the function results can be combined.

[Anonymous]

For this example there will always be three processes. I have other reports that contain five, but if we can figure it out using this example I think I can figure out how to apply it to the others.

Would a function be able to loop through a list of all the departments so it could run all at once?

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rVM9D4IwFPwrDTODGI0zoIxC1I0wNEBik/KRUgb+vW1DS4VWgzJQXu7l3t2jR5o6RdlCQquypsB3XMdvW4xySFFTgyusSgYlpMnLrhNtMD4SCwxYqLDMXcznjTuFtO9YcXvE/EziFaBhJvMs3iFBlHnHrDyAZ9MTzvRE9bXr1znCGJKBg3vVl5VFNZgxTxpzEtGUld5OdXX+ERRweGNbhMNVC2lejK4W9Jlq8DkYZ0MILgYssgVDpmjLYIyehTlt1emCzDew0acS+/8ra8mkZwmllI7ss7Upv/8sWfYC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"Rows", each _, type table }}),
    subtable = (tbl)=>
    let 
        #"Added Index" = Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Process", each try #"Added Index"[Column3]{0} otherwise try #"Added Index"[Column6]{0} otherwise #"Added Index"[Column9]{0}),
        #"Replaced Value" = Table.ReplaceValue(#"Added Custom"," ","Application Name",Replacer.ReplaceValue,{"Column2"}),
        #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Column1],each if [Index]=1 then "Department" else [Column1],Replacer.ReplaceValue,{"Column1"}),
        #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",each [Process],each if [Index]=1 then "Process" else [Process],Replacer.ReplaceValue,{"Process"}),
        #"Removed Top Rows" = Table.Skip(#"Replaced Value2",1),
        #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
        #"Removed Other Columns1" = Table.SelectColumns(#"Promoted Headers",{"Department","Application Name", "Process", "Status", "RTO", "RPO"})
    in
        #"Removed Other Columns1",
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each subtable(Table.SelectColumns([Rows],{"Column1", "Column2", "Column3", "Column4", "Column5"})) & 
subtable(Table.SelectColumns([Rows],{"Column1", "Column2", "Column6", "Column7", "Column8"})) &
subtable(Table.SelectColumns([Rows],{"Column1", "Column2", "Column9", "Column10", "Column11"}))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Department", "Application Name", "Process", "Status", "RTO", "RPO"}, {"Department", "Application Name", "Process", "Status", "RTO", "RPO"}),
    #"Removed Other Columns2" = Table.SelectColumns(#"Expanded Custom",{"Department", "Application Name", "Process", "Status", "RTO", "RPO"})
in
    #"Removed Other Columns2"