Combine data from multiple rows into a single row

wildjosher 

I'd suggest bit different approach for such transformation

In query first pivot the table on column What Section with values Did They..

Remove ID if you don't need it and Group by email.

Resulting tables for each record FillDown, FillUp Remove Duplicates to have only one record for each email.

Keep only this resulting column and expand it.

Script is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    PivotSections = Table.Pivot(
        Source,
        List.Distinct(Source[#"What Section"]),
        "What Section", "Did They Finish Section?"
    ),
    RemoveID = Table.RemoveColumns(
        PivotSections,{"ID"}
    ),
    GroupEmails = Table.Group(
        RemoveID, {"Email"},
        {{"Count",
            each _,
            type table
                [
                    Email=text,
                    Admin Section=text,
                    Internal Section=text,
                    External Section=text,
                    All Sections=text
                ]
        }}
    ),
    FillDown = Table.AddColumn(
        GroupEmails,
        "Custom",
        each Table.FillDown([Count],Table.ColumnNames([Count]))
    ),
    FillUp = Table.AddColumn(
        FillDown,
        "Custom.1",
        each Table.FillUp([Custom],Table.ColumnNames([Custom]))
    ),
    KeepDistinct = Table.AddColumn(
        FillUp,
        "Custom.2",
        each Table.Distinct([Custom.1],{"Email"})
    ),
    OnlyFinalTables = Table.SelectColumns(
        KeepDistinct,
        {"Custom.2"}
    ),
    ExpandThem = Table.ExpandTableColumn(
        OnlyFinalTables,
        "Custom.2",
        {"Email", "Admin Section", "Internal Section", "External Section", "All Sections"},
        {"Email", "Admin Section", "Internal Section", "External Section", "All Sections"}
    )
in
    ExpandThem

and in attached file

Jilly , in addition to JKPieterse  comment. I assumed reporters are in one cell separated by semicolons. If you'd like to split them on separate cells you shall take care about variable number of persons reported per incident (thus variable number of columns). One option is to define number of columns which is more than any possible maximum.

Another option, to fit number of columns into actual current maximum, requires couple of line of additional M script code:

    // Last step of previous version
    RemoveUnused = Table.SelectColumns(RenameBack,
        {"Incident #", "Persons reporting", "Location of Incident"}),

    // Number of semicolons in each record for Persons Reporting
    SepsPerRecord = Table.AddColumn(RemoveUnused, "SepsInString",
        each    Text.Length([Persons reporting])-
                Text.Length(Text.Replace([Persons reporting],";","")) ),

    // Max fpr all records number of semicolns 
    MaxSeps = List.Max(SepsPerRecord[SepsInString]),

    // Generate list of sequental column names
    // from "Reporter 1" to "Reporter <MaxSeps+1>"
    ReportersColumnNames = List.Transform({1..MaxSeps+1},
        each "Person " & Number.ToText(_)),

    // Make standard split and substiture automatically
    // generates list with column names on ReportersColumnNames
    // Another option is to set number of columns which is 
    // definetelly more than possible maximum.
    SplitReporters = Table.SplitColumn(RemoveUnused,
        "Persons reporting",
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),
        ReportersColumnNames)

in
    SplitReporters

Jilly 

Hi again Serge.  I managed to follow the query you set up when I applied it to the full dataset.  I have one problem.  When I get to the stage where I split the concatenated column where a semicolon exists, it only splits the cell into 2 columns even if there are more than 2 semicolons (more than 2 combinations of person and role).  Could you please tell me where the code is forcing the output to 2 columns only?