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.

Answer 1

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