Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
ChemEnger
Advocate IV
Advocate IV

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!

1 ACCEPTED SOLUTION
p45cal
Resolver III
Resolver III

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.

 

View solution in original post

5 REPLIES 5
p45cal
Resolver III
Resolver III

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.

ChemEnger
Advocate IV
Advocate IV

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)

HotChilli
Super User
Super User

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?

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.