Forum Discussion

ChemEnger's avatar
ChemEnger
Advocate V
2 years ago
Solved

Create table from columns, defined from SelectRows

I have a table of Results by Analyst and would like to run an ANOVA in Excel.  I am using Power Query to split the data into separate columns by Analyst:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZNLDoMwDESvUrGuEAkUnGXLroBUUcQGcf9rVCKfeoITscvTOHE8treteE/FvVCmpGK/O9JVWadImdS51IyoRE0dNH15roN0BYpJxZ00c/GNc1w/jVayZa2DpQ6IMtRCgkagkMJIGQN2DOPP2ZTP/i8JZ/fg+OHJZSJBey2WHkC1QMKUhHue0EAFVF+mRrB6HW52qFDU8FEFxDU/jE5j/SLoJbkSPBmBsFg0Qp6InIbUZu7hKnTCK/4vrbCUclMUNxfL430m2GyDZqLtYDqdFGluCKyMCefNFjov/I8Hua2YoflCXMiNFJYuwiaL+eBIBTOiFZPm2PdWJycyF2nwvP8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Analyst = _t, Result = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Analyst", type text}, {"Result", type number}}),
    // Buffer source table, as we want to use it more than once
    Buffer = Table.Buffer(#"Changed Type"),
    // Get a distinct list of analysts
    Analysts = List.Distinct(Buffer[Analyst]),
    // Count the number of analysts, create a zero-indexed list
    Index = List.Transform({0..List.Count(Analysts)-1}, each _),

    // Function to create a list of results for each indexed analyst
    fnTable = (i) =>
    let
        Results = Table.SelectRows(Buffer, each [Analyst] = Analysts{i})[Result]
    in
        Results,

    // Create a list of lists for set of results
    ListOfColumns = List.Transform(Index, each fnTable(_)),
    // Create anonymised column headings (Analyst 1 .. Analyst n)
    ColumnHeadings = List.Transform(Index, each Number.ToText(_+1,  "Analyst 0")),
    // Create a table, with each Analyst in a separate column
    TableFromColumns = Table.FromColumns(ListOfColumns, ColumnHeadings)
in
    TableFromColumns

 

I am quite happy with the outcome, but as per the example in another Community post, I feel there should be a way to neatly combine the fnTable function into the ListOfColumns line, plus any other efficiences greatly appreciated!

  • How about doing without the function and using Group By in the Transform tab instead, grouping on the Analyst column:

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZNLDoMwDESvUrGuEAkUnGXLroBUUcQGcf9rVCKfeoITscvTOHE8treteE/FvVCmpGK/O9JVWadImdS51IyoRE0dNH15roN0BYpJxZ00c/GNc1w/jVayZa2DpQ6IMtRCgkagkMJIGQN2DOPP2ZTP/i8JZ/fg+OHJZSJBey2WHkC1QMKUhHue0EAFVF+mRrB6HW52qFDU8FEFxDU/jE5j/SLoJbkSPBmBsFg0Qp6InIbUZu7hKnTCK/4vrbCUclMUNxfL430m2GyDZqLtYDqdFGluCKyMCefNFjov/I8Hua2YoflCXMiNFJYuwiaL+eBIBTOiFZPm2PdWJycyF2nwvP8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Analyst = _t, Result = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Result", type number}}),
        GroupedRows = Table.Group(#"Changed Type", {"Analyst"}, {{"grp", each _[Result]}})[grp],
        Headers = List.Transform({1..List.Count(GroupedRows)}, each Number.ToText(_,  "Analyst 0")),
        Finish = Table.FromColumns(GroupedRows, Headers)
    in
        Finish

     

    The GroupedRows step returns only the [grp] column of the grouped tables, which makes it a list.

     

5 Replies

  • p45cal's avatar
    p45cal
    Solution Supplier

    How about doing without the function and using Group By in the Transform tab instead, grouping on the Analyst column:

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZNLDoMwDESvUrGuEAkUnGXLroBUUcQGcf9rVCKfeoITscvTOHE8treteE/FvVCmpGK/O9JVWadImdS51IyoRE0dNH15roN0BYpJxZ00c/GNc1w/jVayZa2DpQ6IMtRCgkagkMJIGQN2DOPP2ZTP/i8JZ/fg+OHJZSJBey2WHkC1QMKUhHue0EAFVF+mRrB6HW52qFDU8FEFxDU/jE5j/SLoJbkSPBmBsFg0Qp6InIbUZu7hKnTCK/4vrbCUclMUNxfL430m2GyDZqLtYDqdFGluCKyMCefNFjov/I8Hua2YoflCXMiNFJYuwiaL+eBIBTOiFZPm2PdWJycyF2nwvP8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Analyst = _t, Result = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Result", type number}}),
        GroupedRows = Table.Group(#"Changed Type", {"Analyst"}, {{"grp", each _[Result]}})[grp],
        Headers = List.Transform({1..List.Count(GroupedRows)}, each Number.ToText(_,  "Analyst 0")),
        Finish = Table.FromColumns(GroupedRows, Headers)
    in
        Finish

     

    The GroupedRows step returns only the [grp] column of the grouped tables, which makes it a list.

     

    • ChemEnger's avatar
      ChemEnger
      Advocate V

      Nice one p45cal,

       

      I thought Table.Group should do something but hadn't considered returning a selected column.

  • HotChilli's avatar
    HotChilli
    Community Champion

    You've got a method that works, it's orderly, it's commented (a little excessively but that may be a personal thing), it can be maintained, it seems to be performant ... and you want to do what?

     

  • Thanks HotChilli,

     

    The comments are mainly for the benefit of this Forum!  All I was after was whether I could / should incorporate the

    Table.SelectRows(Buffer, each [Analyst] = Analysts{i})[Result]

    from fnTable into a single line nstead of creating it separately and calling it from 

    List.Transform(Index, each fnTable(_))

     

    • p45cal's avatar
      p45cal
      Solution Supplier

      I'd be interested in someone showing how to do this (bringing the function into a single line)