Transpose/Transform Multiple Rows into Single Row

v-yulgu-msft, 

Well it took a short 2 hour Saturday road trip for me to come up with the realization/solution.

You're absolutely correct, Why do you need to "merge [Manager] & [Email] then splitting them"?. This is exactly what I was thinking too.

Basically, I did not think to Table.Group( ) by more than one field.

To bring closure to the question, sample data provided below:

let

    Source = Excel.CurrentWorkbook(){[Name="MergeTable"]}[Content],
    #"Removed Duplicates" = Table.Distinct(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"ManagerName", type text}, {"Email", type text}, {"DeptID", type text}, {"DeptDesc", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "ManagerName", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"LastName", "FirstName"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"FirstName", "LastName"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"ManagerName"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"DeptID", "DeptDesc"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"DeptInfo"),
    // Function that adds an incrementing index number
    AddRanking = (table, column, newColumn) =>
        Table.AddIndexColumn(Table.Sort(table, {{column, Order.Ascending}}), newColumn, 1, 1),
    #"Grouped Rows" = Table.Group(#"Merged Columns1", {"ManagerName", "Email"}, {{"Data", each _, type table}}),
    Transformed = Table.TransformColumns(#"Grouped Rows", {{"Data", each AddRanking(_, "DeptInfo", "Rank")}}),
    #"Expand Data" = Table.ExpandTableColumn(Transformed, "Data", {"DeptInfo", "Rank"}, {"DeptInfo", "Rank"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expand Data", {{"Rank", type text}}, "en-US")[Rank]), "Rank", "DeptInfo")

in
    #"Pivoted Column"

Expected Table results for an MSWord Email Merge where each manager gets one email with a list of their department's.