Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
56 | |
27 | |
17 | |
13 |