Forum Discussion

clgrantmidd's avatar
clgrantmidd
Frequent Visitor
6 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago
    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's avatar
    Anonymous
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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"