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.
Community ChampionOK, 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?