Forum Discussion
Specific rows to column
I have a report in Excel that has information in specific rows that I need to convert to a column. I have tried group/pivot/unpivot in many different ways and I can't seem to figure it out. Here is what the source data looks like:
| department A | locations | New York | Washington | Arizona |
| department A | position A | remote | in office | in office |
| department A | position B | remote | remote | |
| department A | position C | in office | in office | |
| department B | locations | New York | Wisconsin | |
| department B | position A | remote | in office | |
| department B | position B | remote | in office | |
| department C | locations | California | Nevada | |
| department C | position D | remote | remote | |
| department D | locations | Texas | Arizona | |
| department D | position A | in office | in office |
I need to take the rows for "locations" and convert them to a column for each department/position. Any ideas? Here is what the final output should look like:
| Department | Location | Position | status |
| department A | New York | position A | remote |
| department A | New York | position B | in office |
| department A | New York | position C | in office |
| department A | Washington | position A | remote |
| department A | Washington | position C | in office |
| department A | Arizona | position A | in office |
| department A | Arizona | position B | remote |
| department B | New York | position A | remote |
| department B | New York | position B | remote |
| department B | Wisconsin | position A | in office |
| department B | Wisconsin | position B | in office |
| department C | California | position D | remote |
| department C | Nevada | position D | remote |
| department D | Texas | position A | in office |
| department D | Arizona | position A | in office |
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.
10 Replies
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?
Select the first 2 columns and 'Unpivot other columns'
That didn't work. I should have mentioned the source data didn't have any column headers, so technically it looks like this in Power Query:
Column1 Column2 Column3 Column4 Column5 department A locations New York Washington Arizona department A position A remote in office in office department A position B remote remote department A position C in office in office department B locations New York Wisconsin department B position A remote in office department B position B remote in office department C locations California Nevada department C position D remote remote department D locations Texas Arizona department D position A in office in office
"That didn't work" - In the spirit of cooperation, tell me what happened. Error messages? Syntax correct but not the desired result? Also please paste the line of Power Query code that was generated when you Unpivoted the columns.
Right, I see that the original table is really made up of mini-tables for each department.
Is this the final table or are there more departments in a real table?
---
I'll have a look at a generic solution.
A quick, semi-manual solution would be to make 4 versions of the original table (each one filtered to a single, different department).
Promote the headers. Then do the Unpivot.
You could then append the 4 tables together.
The full table includes 30 departments and roughly a dozen locations, with up to 10 positions for each department/location. I am able to get the table to show department/position or department/location but I can't figure out how to get them all in one table with 4 columns (Department, Location, Position, Status)
Can we rely on there being 11 columns maximum?
So we could make 3 queries from this one -1st one -> Columns 1,2,3,4,5
2nd one -> Columns 1,2,6,7,8
3rd one -> Columns 1,2,9,10,11
We can do this manually in the first instance.
Rename the Columns to be 1,2,3,4,5 in each query then Append all 3 tables together.
Then a bit of manipulation to get the final result.
Let me know if that is a good plan.