Forum Discussion
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 FinishThe GroupedRows step returns only the [grp] column of the grouped tables, which makes it a list.
5 Replies
- p45calSolution 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 FinishThe GroupedRows step returns only the [grp] column of the grouped tables, which makes it a list.
- HotChilliCommunity 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?
- ChemEngerAdvocate V
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(_))
- p45calSolution Supplier
I'd be interested in someone showing how to do this (bringing the function into a single line)