Forum Discussion
Transpose a Group of Multiple Rows to Multiple columns
- Hi
The two pieces of code in my post work together. Just paste your table name in the 'Source = ...'
To make it something like:
Source = #"mytable",
// where mytable is the name of you original query/table
//do not forget the closing comma at the end of the line.
The first piece of code will call the function fTranspose in this line:
#"Grouped Rows" = Table.Group(Source, {"Fund"}, {{"Data", each fTranspose(_, {"DonorID", "DonorName", "DonorCity", "DonorYear"})}}),
And then process to the result.
Kind regards
JB You sound like you were heading in the right direction Carol. The trick is to add an index column during your grouping to get the number of columns. After expanding, then unpivot/merge columns and repivot. Below is a sample presuming that your data is in an internal excel table.
let Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content], #"Grouped Rows" = Table.Group(Source, {"Fund"}, {{"AllRows", each Table.AddIndexColumn(_, "Index", 0, 1) , type table}}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"DonorID", "DonorName", "DonorCity", "DonorYear", "Index"}, {"DonorID", "DonorName", "DonorCity", "DonorYear", "Index"}), IndexToText = Table.TransformColumns(#"Expanded AllRows",{{"Index", each if _ = 0 then "" else Text.From(_), type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(IndexToText, {"Fund", "Index"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Index", type text}}, "en-US"),{"Attribute", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"), #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Min) in #"Pivoted Column"
6 Replies
You sound like you were heading in the right direction Carol. The trick is to add an index column during your grouping to get the number of columns. After expanding, then unpivot/merge columns and repivot. Below is a sample presuming that your data is in an internal excel table.
let Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content], #"Grouped Rows" = Table.Group(Source, {"Fund"}, {{"AllRows", each Table.AddIndexColumn(_, "Index", 0, 1) , type table}}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"DonorID", "DonorName", "DonorCity", "DonorYear", "Index"}, {"DonorID", "DonorName", "DonorCity", "DonorYear", "Index"}), IndexToText = Table.TransformColumns(#"Expanded AllRows",{{"Index", each if _ = 0 then "" else Text.From(_), type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(IndexToText, {"Fund", "Index"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Index", type text}}, "en-US"),{"Attribute", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"), #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value", List.Min) in #"Pivoted Column"Hi clgrantmidd ,
This is technically achievable and reasonably simple, but I have to ask - do you really need it? What you are trying to achieve is quite bad design, please make sure you know what you do. 😀
This is the main table query (Source = the table in your original post):
let Source = ... #"Grouped Rows" = Table.Group(Source, {"Fund"}, {{"Data", each fTranspose(_, {"DonorID", "DonorName", "DonorCity", "DonorYear"})}}), NewHeaders = List.Distinct(List.Combine(Table.AddColumn(#"Grouped Rows", "Columns", each [Data]{1})[Columns])), ExtractTables = Table.AddColumn(#"Grouped Rows", "Table", each [Data]{0}), ExtractValues = Table.ExpandTableColumn(ExtractTables, "Table", NewHeaders), #"Removed Other Columns" = Table.SelectColumns(ExtractValues,List.Combine({{"Fund"}, NewHeaders})) in #"Removed Other Columns"In the Table.Group you need to pass a list of the fields you are trying to roll, if the sample table is what you need it will work as it is.
This is the function fTranspose called in the main query, create a blank query, rename and copy the content from below:
(pTable as table, pHeaders as list)=> let Source = pTable, NewHeaders = pHeaders, CutTable = Table.SelectColumns(Source,NewHeaders), Accumulated = List.Accumulate( Table.ToRows(CutTable), {{}, {}, 1}, (seed, add)=> {seed{0} & add, seed{1} & List.Transform(NewHeaders, each _ & "_" & Number.ToText(seed{2})), seed{2}+1}), Table = Table.FromRows({Accumulated{0}}, Accumulated{1}), TableType = Value.Type(Table), Output = {Table, Accumulated{1}} in OutputThe function may look complex, but all what it does is stacking the headers together and then it returns a table and column definitions to automate the ExpandTableColumn in the main query.
Kind regards,
JB
JB- thank you for this, you saved me so much work!
I do have a few questions while I'm trying to get the code to work if you have the time.
In the ftranspose function you didn't say what I needed to put in for values (if any) but it does prompt me for some when I go to save the query. I did figure out ptable should equal the query/table I am using so I put in #"mytable" but I am not sure what to do for the NewHeaders. I tried putting in this
{"DonorID", "DonorName", "DonorCity", "DonorYear"}but that didn't seem to work.
Thanks,
Carol
- Hi
The two pieces of code in my post work together. Just paste your table name in the 'Source = ...'
To make it something like:
Source = #"mytable",
// where mytable is the name of you original query/table
//do not forget the closing comma at the end of the line.
The first piece of code will call the function fTranspose in this line:
#"Grouped Rows" = Table.Group(Source, {"Fund"}, {{"Data", each fTranspose(_, {"DonorID", "DonorName", "DonorCity", "DonorYear"})}}),
And then process to the result.
Kind regards
JB
