Create table from columns, defined from SelectRows

[ChemEnger]

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!

[p45cal · Accepted solution]

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]

Nice one @p45cal,

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

[ChemEnger]

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]

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

[HotChilli]

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?