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"

     

clgrantmidd's avatar
clgrantmidd
Frequent Visitor
6 years ago

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

    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

     

    • clgrantmidd's avatar
      clgrantmidd
      Frequent Visitor
      6 years ago

      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