Specific rows to column

[saie5073]

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

[HotChilli]

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.

[saie5073]

Thank you so much for all of your help with this!

I have 2 tables that are similarly structured, one with 32 columns total & the other is 17 columns. The only difference would be the 17-column cable would result in 5 columns, and the 32-column table results in 7

[HotChilli · Accepted solution]

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.

[saie5073]

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? 

[HotChilli]

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.

[saie5073]

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)

[HotChilli]

"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.

[saie5073]

line in Power Query is  Table.UnpivotOtherColumns(Source, {"Column1", "Column2"}, "Attribute", "Value")

here was the result:

Here is the desired result (entered manually)

[HotChilli]

Select the first 2 columns and 'Unpivot other columns'

[saie5073]

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