Group data with dynamic columns

Question

I am working on a system to compare data of QA audits. The data sources contain a dynamic number of columns (based on QA agents) and a dynamic number of rows (based on the QA format).

The system I am creating already provides information such as variance and average per question, however, I am struggling with obtaining an average per QA agent.

The QA formats have sections and each section has a different weight for the overall score. The questions in each section can only be 1, 0 or null. Based on this, the logic I am following is to group each section using average aggregator and then calculating the audit score using the averages and weights.

However, since the number of participants changes from time to time, I am struggling to creating this step. The subsequent steps I already know how to do.

Other things to note:

The column names for the auditors are always "Participant #", which I know is useful to filter out dynamic columns.
There are ~70 unique questions in each format. This is why I have not considered pivoting the table or similar.

Here is a visual example of what I'm working with:

Question	Participant 1	Participant 2	...	Participant n	Q Section
Q1	1	1	...	1	A
Q2	1	1	...	1	A
Q3	1	0	...	1	A
Q4	1	1	...	1	B
Q5	0	1	...	null	C

This is the goal:

Section	Participant 1	Participant 2	...	Participant n
A	100%	66.67%	...	100%
B	100%	100%	...	100%
C	0%	100%	...	null

I've tried using the same logic as I used for the averages:

= Table.AddColumn(
    #"Remove NA text","Average Variable",(row) => List.Average(
        Record.FieldValues(Record.SelectFields(row,
            List.Select(
                Table.ColumnNames(#"Remove NA text"),
                each Text.Contains(_,"Participant"))
            )
        )
    )
,Percentage.Type)

And the logic I used to calculate variance:

= Table.AddColumn(
    #"Average","Variance", (row) => List.Sum(
        List.Transform(
            Record.FieldValues(
                Record.SelectFields(
                    row,
                    List.Select(
                        Table.ColumnNames(#"Remove NA text"),
                        each Text.Contains(_,"Participant")
                    )
                )
            ),
            each Number.Power(_ - Record.Field(row,"Average Variable"),2)
        )
    )/(Record.FieldCount(row)-1),
type number)

But I am struggling to keep the list of data from each column (using the Table.Group function) to calculate the average for each section for each column in a dynamic way.

Ron Rosenfeld · Accepted Answer · 2025-08-09 12:15:20Z

2

You can attain your goal by pivoting on the participants. But first you have to unpivot the table.

UNPIVOT all the Partipant columns
Delete the Question Column
PIVOT the Attribute column aggregating by Average
Set the data type to Percentage
The code should adjust to any number of Participant columns

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    Participants = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_,"Participant")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Question", type text}, {"Q Section", type text}} &
            List.Transform(Participants, each {_, Int64.Type})),
    #"Unpivoted Participant Columns" = Table.Unpivot(#"Changed Type",Participants, "Attribute", "Value"),
    #"Removed Columns" = Table.SelectColumns(#"Unpivoted Participant Columns",{"Q Section","Attribute", "Value"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value", List.Average),
    #"Set Types" = Table.TransformColumnTypes(#"Pivoted Column", List.Transform(List.Select(Table.ColumnNames(Source), 
    each Text.StartsWith(_,"Participant")), each {_, Percentage.Type}))
in
    #"Set Types"

Results from your posted data

edited Aug 9 at 12:15

answered Aug 9 at 1:14

Ron Rosenfeld

62.1k7 gold badges35 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Promethee Aug 10 at 9:25

Why not stop after unpivoting then add the table to the data model and use some pivot table(s) for every metrics?

Ron Rosenfeld Aug 10 at 11:49

@Promethee I think it depends on what you want for output and how you want it formatted. For separate tables, it is arguably simpler in PQ. For a combined table, if you are OK with the available Pivot Table layouts, that could work also. But you could also do that in PQ. To attain the OP's stated "goal", PQ is simpler. If he prefers something else, it would depend on exactly what he wants as to the most appropriate method. But, as is frequently the case, there are different ways to solve the same problem.

Collectives™ on Stack Overflow

Group data with dynamic columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related