March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
34 | |
30 | |
20 | |
19 | |
12 |