cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Transpose a Group of Multiple Rows to Multiple columns

Transpose a Group of Multiple Rows to Multiple columns
 
 

Hi everyone - I've been searching for an answer to do this using Power M but I can't seem to find the correct coding. 

I'd like to group them by Fund then have each row with new columns.

The issue is I don't know how many donors each fund will have so it would have to count the rows first.

Raw Data:

FundDonorIDDonorNameDonorCityDonorYear
1233056789Sally SmithAnywhere2002
1233012345Joe WarrenDisneyland2015
1233099000Daffy DuckOrlando2013
1455867589Bugs BunnyLA2006
3356676532Ronald McDonaldNashville2010
3356676532Ronald McDonaldNashville2010
3355644532Mickey MouseSavannah2017
3355678456George McSauceWashington2005

 

Result:

 

FundDonorIDDonorNameDonorCityDonorYearDonorID1DonorName1DonorCity1DonorYear1DonorID2DonorName2DonorCity2DonorYear2DonorID3DonorName3DonorCtiy3DonorYear3
1233056789Sally SmithAnywhere200212345Joe WarrenDisneyland201599000Daffy DuckOrlando2013    
1455867589Bugs BunnyLA2006            
3356676532Ronald McDonaldNashville201076532Ronald McDonaldNashville201076532Ronald McDonaldNashville201044532Mickey MouseSavannah2017

 

I've tried many things using Index, AddingRank, Group then Table.Transpose, Pivot, Unpivot but I don't think I have the correct fields where they should be.

 

Any help would be greatly appreciated!

Thanks,

Carol Grant

Middlebury College

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User I
Super User I

Re: 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

View solution in original post

Highlighted
Solution Specialist
Solution Specialist

Re: Transpose a Group of Multiple Rows to Multiple columns

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"

 

View solution in original post

6 REPLIES 6
Highlighted
Super User I
Super User I

Re: Transpose a Group of Multiple Rows to Multiple columns

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
    Output

 

The 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

Highlighted
Frequent Visitor

Re: Transpose a Group of Multiple Rows to Multiple columns

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

 

Highlighted
Super User I
Super User I

Re: 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

View solution in original post

Highlighted
Solution Specialist
Solution Specialist

Re: Transpose a Group of Multiple Rows to Multiple columns

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"

 

View solution in original post

Highlighted
Super User I
Super User I

Re: Transpose a Group of Multiple Rows to Multiple columns

hi @clgrantmidd 

 

This is a version which combines both parts into one query:

 

(myTable as table, pDriverColumn as text, pHeaders as list)=>
let
    mfTranspose = (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
        Output,

    Source = myTable,
    mDriverColumn = pDriverColumn,
    mHeaders = pHeaders,
    #"Grouped Rows" = Table.Group(Source, {mDriverColumn}, {{"Data", each mfTranspose(_, mHeaders)}}),
    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({{mDriverColumn}, NewHeaders}))
in
    #"Removed Other Columns"

 

Please create a Blank Query, rename it to clgrantmidd and in the advanced editor replace the content with the above code.

The function takes three arguments: the original table, the column you use as a driver (in your case "Fund") and the list of columns that need to be "transposed" (basically all other columns in your example). You can use it in this manner:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZA7a8MwFIX/ivCcQbZe9phgCJS6hXrIEDIIR7FF1CuQoxT9+15LQ8nc6dxzOR/3cT5XdcMYrXaVkKrtUEftXCLjt30s6PaQfhYTDJYNpU112f0RqFygvnlDTjoEA2h6u4JJTsM1I7V4QbqO0k17fbsl0sfpjuYzbHFf8qzkuRAtNqQSealDnFdyiAAJzfu+bCNzlDEhJTaUFKxB/fKg3ZUMU58L7HzodXla58oRNf0nJjaM84INdrqbRAYfV5Of99QAeimIekFUy7MejQ+zwUmjjtMGnXCQhfnhodyFL7v8Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Fund = _t, DonorID = _t, DonorName = _t, DonorCity = _t, DonorYear = _t]),
    
    Result = clgrantmidd(Source, "Fund", {"DonorID", "DonorName", "DonorCity", "DonorYear"})
in
    Result

 

 

Kind regards,

JB

 

Highlighted
Frequent Visitor

Re: Transpose a Group of Multiple Rows to Multiple columns

Thank you both for your time and energy on this for me. 

 

I finally got back to it and it and I tried out mcybulski's script also.  That one seemed to work and it looked a little easier for me to understand.  Thank you mcybulski!

 

JB- thank you for your input also, I will be referring to your script when I get more advanced.

 

-Carol

 

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021