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 :

---

let me know how you get on.