Transpose Excel columns while stacking grouped columns

[culper76]

I've looked through quite a few similar postings and articles but so far haven't found anything that really solves the issue (this very well could be due to my inexperience with the formulas and lack of macros). Essentially, I'm trying to transpose a large amount of rows but maintaining groups. I almost had luck with an =Offset fix I saw but was struggling to get the rows to repeat in the groupings. The issue I've had here is it still seems to go to one column and I'd have to redo or manually slice up the overall column. If I transpose the entire data set then I have the columns in the correct order, but would still have to rearrange the columns and rows in a zigzagging manner. Here's an example of my data and where I need to get it:

Original:

<table><tbody><tr><th> </th><th>msa1</th><th>msa2</th><th>msa3</th><th>unem1</th><th>unem2</th><th>unem3</th></tr><tr><td>city1</td><td>1001</td><td>1002</td><td>1003</td><td>6.2</td><td>6.3</td><td>5.5</td></tr><tr><td>city2</td><td>2001</td><td>2002</td><td>2003</td><td>5.7</td><td>5.3</td><td>4.5</td></tr><tr><td>city3</td><td>3001</td><td>3002</td><td>3003</td><td>7.8</td><td>8.3</td><td>7.1</td></tr></tbody></table>

Transposed:

<table><tbody><tr><th> </th><th>city1</th><th>city2</th><th>city3</th></tr><tr><td>msa1</td><td>1001</td><td>2001</td><td>3001</td></tr><tr><td>msa2</td><td>1002</td><td>2002</td><td>3002</td></tr><tr><td>msa3</td><td>1003</td><td>2003</td><td>3003</td></tr><tr><td>unem1</td><td>6.2</td><td>5.7</td><td>7.8</td></tr><tr><td>unem2</td><td>6.3</td><td>5.3</td><td>8.3</td></tr><tr><td>unem3</td><td>5.5</td><td>4.5</td><td>7.1</td></tr></tbody></table>

End product:

<table><tbody><tr><th> </th><th>msa</th><th>unem</th></tr><tr><td>city1</td><td>1001</td><td>6.2</td></tr><tr><td>city1</td><td>1002</td><td>6.3</td></tr><tr><td>city1</td><td>1003</td><td>5.5</td></tr><tr><td>city2</td><td>2001</td><td>5.7</td></tr><tr><td>city2</td><td>2002</td><td>5.3</td></tr><tr><td>city2</td><td>2003</td><td>4.5</td></tr><tr><td>city3</td><td>3001</td><td>7.8</td></tr><tr><td>city3</td><td>3002</td><td>8.3</td></tr><tr><td>city3</td><td>3003</td><td>7.1</td></tr></tbody></table>

Does anyone have any ideas using either the original or transposed data that would get to the end product? Thanks in advance

 

[sandy666]

just for fun

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    unem = Table.UnpivotOtherColumns(Source, {"city", "unem1", "unem2", "unem3"}, "Attribute", "unem"),
    msa = Table.UnpivotOtherColumns(unem, {"city", "Attribute", "unem"}, "Attribute.1", "msa"),
    group = Table.Group(msa, {"city", "unem"}, {{"table", each _, type table}}),
    IndexMod = Table.TransformColumns(Table.AddIndexColumn(group, "IndexMod", 0, 1), {{"IndexMod", each Number.Mod(_, 3), type number}}),
    extract = Table.AddColumn(IndexMod, "msa", each Table.Column([table],"msa"){[IndexMod]}),
    TSC = Table.SelectColumns(extract,{"city", "unem", "msa"})
in
    TSC

 

[culper76]

Thanks for the replies and help, looks like I finally got it iron out! Appreciate it

 

[culper76]

maybe if you post representative source data and expected result

Clip of source data and desired result above, but I got it ironed out with the other solutions. Thanks!

 

[lrobbo314]

Here is a totally different way to go about this in PQ

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Transform = Table.FromList(Table.TransformRows(Source, (row) => Table.FromRows({{row[Column1],row[msa1],row[unem1]},{row[Column1],row[msa2],row[unem2]},{row[Column1],row[msa3],row[unem3]}},{"city","msa","unem"})), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Extract = Table.ExpandTableColumn(Transform, "Column1", {"city", "msa", "unem"}, {"city", "msa", "unem"})
in
    Extract