Forum Discussion
Specific rows to column
OK, full Advanced Editor Code here:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBDsIgEEWvQlj3Ei2sXZkY03QxoZBMbJkGiBpPL3ShSARd8Yfk8YY/jnzWG7iwahtYzzu+kceAZPfB6ZWCjgEtI2NQfeapq+NDjrP30GJE1ZSeKMgkWEhBIn3MB31jZ3KXGE/oVbxEW+N+f7KNDX9jothSwIKGnEXYV77CDDXsZZO5Leu0YGShOuo7pLN3+CD7VSPLLhrtT08=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"department A" = _t, locations = _t, #"New York" = _t, Washington = _t, Arizona = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"department A", type text}, {"locations", type text}, {"New York", type text}, {"Washington", type text}, {"Arizona", type text}}), #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"), #"Grouped Rows" = Table.Group(#"Demoted Headers", {"Column1"}, {{"all", each _, type table [Column2=nullable text, Column3=nullable text, Column4=nullable text, Column5=nullable text]}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom1", each Table.PromoteHeaders([all])), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"all"}), #"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Custom2", each Table.RemoveColumns([Custom1], Table.ColumnNames ([Custom1]) {0} )), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Custom1"}), #"Added Custom" = Table.AddColumn(#"Removed Columns2", "Custom", each Table.UnpivotOtherColumns([Custom2], {"locations"}, "Attribute", "Value")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom2"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"locations", "Attribute", "Value"}, {"Custom.locations", "Custom.Attribute", "Custom.Value"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Column1", type text}, {"Custom.locations", type text}, {"Custom.Attribute", type text}, {"Custom.Value", type text}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Custom.Value] <> " ")), #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Column1", Order.Ascending}, {"Custom.Attribute", Order.Ascending}, {"Custom.locations", Order.Ascending}}) in #"Sorted Rows"Some of these steps could possibly be neatened up but sometimes it's easier to add a column at each stage so that I can compare it to the previous stage easily. Then I remove the old column and move on.
The algorithm is basically :
Group the table by department and create a mini-table on each row.
Within each row, do some tidying to make all mini-tables have the same column names.
and Unpivot the Geographic column names.
Expand the mini-tables.
Remove the rows with blank values.
---
See how you get on with your test data and then try the full set.
--
I think there is a mistake in the provided desired table :
department A New York position B in office ---
let me know how you get on.
OK, full Advanced Editor Code here:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBDsIgEEWvQlj3Ei2sXZkY03QxoZBMbJkGiBpPL3ShSARd8Yfk8YY/jnzWG7iwahtYzzu+kceAZPfB6ZWCjgEtI2NQfeapq+NDjrP30GJE1ZSeKMgkWEhBIn3MB31jZ3KXGE/oVbxEW+N+f7KNDX9jothSwIKGnEXYV77CDDXsZZO5Leu0YGShOuo7pLN3+CD7VSPLLhrtT08=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"department A" = _t, locations = _t, #"New York" = _t, Washington = _t, Arizona = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"department A", type text}, {"locations", type text}, {"New York", type text}, {"Washington", type text}, {"Arizona", type text}}),
#"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
#"Grouped Rows" = Table.Group(#"Demoted Headers", {"Column1"}, {{"all", each _, type table [Column2=nullable text, Column3=nullable text, Column4=nullable text, Column5=nullable text]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom1", each Table.PromoteHeaders([all])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"all"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Custom2", each Table.RemoveColumns([Custom1], Table.ColumnNames ([Custom1]) {0} )),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"Custom1"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns2", "Custom", each Table.UnpivotOtherColumns([Custom2], {"locations"}, "Attribute", "Value")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom2"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"locations", "Attribute", "Value"}, {"Custom.locations", "Custom.Attribute", "Custom.Value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Column1", type text}, {"Custom.locations", type text}, {"Custom.Attribute", type text}, {"Custom.Value", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Custom.Value] <> " ")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Column1", Order.Ascending}, {"Custom.Attribute", Order.Ascending}, {"Custom.locations", Order.Ascending}})
in
#"Sorted Rows"Some of these steps could possibly be neatened up but sometimes it's easier to add a column at each stage so that I can compare it to the previous stage easily. Then I remove the old column and move on.
The algorithm is basically :
Group the table by department and create a mini-table on each row.
Within each row, do some tidying to make all mini-tables have the same column names.
and Unpivot the Geographic column names.
Expand the mini-tables.
Remove the rows with blank values.
---
See how you get on with your test data and then try the full set.
--
I think there is a mistake in the provided desired table :
| department A | New York | position B | in office |
---
let me know how you get on.
This worked great, thanks! I have a similar issue with another table which is a little more complicated but I think the principal is the same so I'll post it here. Here is my source table from an Excel report:
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 now I have 2 different rows need to be converted into columns, with this as the final output:
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 I tried using your previous method (which worked like a charm) but i think the blank values in Column2 are causing issues. Any ideas?