Forum Discussion
Working with column subheadings in Excel
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 f...
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"
lbendlin
Super User
Super UserWill 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.
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
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"