What's the best function to switch rows and columns in a table?-CodePudding

I’m running a compliance project that has work we need to do (WorkToDo column) mapped to each law that requires we do it (Law#1, Law#2, Law#3):

I’d like to generate a pivot table that maps that shows each law mapped to the work required by it:

What’s the best way to do this?

CodePudding user response：

In powerquery, dynamic for number of columns and rows

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"WorkToDo"}, "Attribute", "Value"),
x = Table.Group(  #"Unpivoted Other Columns", {"Attribute"}, {{"Concat", each Text.Combine([WorkToDo],":"), type text}}),
DynamicColumnList  = List.Transform({1 ..List.Max(Table.AddColumn(x,"Custom", each List.Count(Text.PositionOfAny([Concat],{":"},Occurrence.All)))[Custom]) 1}, each "Item." & Text.From(_)),
#"Split Column by Delimiter" =  Table.SplitColumn( x, "Concat",  Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), DynamicColumnList)
in #"Split Column by Delimiter"

CodePudding user response：

You can do it in Power Query

Close and Load

CodePudding user response：

Please see the steps on the picture. In this way you don't get empty cells in between.