Forum Discussion

Jilly's avatar
Jilly
Copper Contributor
May 07, 2019
Solved

Combine data from multiple rows into a single row

Hi Excel community   I have a dataset that looks something like this   Incident #             Name of person reporting           Role of person reporting             Location of Incident   123  ...
excel
wildjosher's avatar
wildjosher
Copper Contributor
Aug 14, 2019

SergeiBaklan 

 

I am hoping to achieve a similar outcome, however I was unable to edit your code properly.

 

I am trying to get this:

 

To become like this: 

 

 

Using this Code:

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    GroupByEmail = Table.Group({"Email"}, {{"What Section", each _, type table [Email=text, What Section=text]}}),
    ListWithInfo = Table.AddColumn(GroupByEmail, "What Section", each [What Section][What Section]),
    ExtractFromList = Table.TransformColumns(ListWithWhatSection, {"What Section", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    SplitEmail = Table.SplitColumn(ExtractFromList, "Email", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Email.1", "Email.2"}),
    ToNumber = Table.TransformColumnTypes(SplitEmail,{{"Email.1", Int64.Type}, {"Email.2", type text}}),
    RemoveUnused = Table.SelectColumns("ID", "Did They Finish Section?")
in
    RemoveUnused

 

 

But I must have broken something along the way because I get this error: 

 

Expression.Error: We cannot convert the value "ID" to type Table. Details: Value=ID Type=Type

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 14, 2019

    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

Resources