Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
clgrantmidd
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
Anonymous
Not applicable

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

Anonymous
Not applicable

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
Anonymous
Not applicable

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"

 

Anonymous
Not applicable

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

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

 

Anonymous
Not applicable

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
Anonymous
Not applicable

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

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors