Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to 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.
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.
Nice one @p45cal,
I thought Table.Group should do something but hadn't considered returning a selected column.
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(_))
I'd be interested in someone showing how to do this (bringing the function into a single line)
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
29 | |
12 | |
12 | |
11 | |
8 |
User | Count |
---|---|
53 | |
28 | |
15 | |
14 | |
13 |