Transpose/Transform Multiple Rows into Single Row

Hello, 

First thank you all for the work you do. This is my first time posting to the community however, I've been using your posts as guides for awhile. Most recently how to merge multiple rows into one row with different column based on row values.

Project Background

Every year I help another department produce an Email Merge using a .xlsx file. This year, I got tired of 

CTRL C ->  Up Arrow (x times) -> Right Arrow (y times) -> CTRL V -> CTRL -> T

 so I decided to search for a Power Query solution, again thanks to your community.

You may ask, "Why doesn't your data warehouse write the query", and simply put, we work for the state. The request has been submitted for a number of years.

Using v-yulgu-msft's example:

I was able to take the 2-column example and make my data "fit". However I think there may be a better solution than what I've done.

let
    // Referencing an existing query called 'InvList'
    // This removed columns that were not necessary for the merge
    Source = #"InvList",
    
    // Splitting [Manager] into two fields; originally 'LastName,First'
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Manager", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, false), {"Manager.1", "Manager.2"}),

    // Rename the two newly created fields [LastName] & [FirstName]
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Manager.1", "LastName"}, {"Manager.2", "FirstName"}}),

    // Merge the [FirstName]&" "&[LastName] into [Manager]
    #"Merged Manager Name" = Table.CombineColumns(#"Renamed Columns",{"FirstName", "LastName"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Manager"),
    #"Merged Columns" = Table.CombineColumns(#"Merged Manager Name",{"Manager", "EmailAddress"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Manager.1"),

    // Merge [DeptID]&" = "&[Department Description] to be a new field called [Merged]
    #"Merged DeptInfo" = Table.CombineColumns(#"Merged Columns",{"DeptID", "Department Description"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged"),

    // Function that adds an incrementing index number
    AddRanking = (table, column, newColumn) =>
        Table.AddIndexColumn(Table.Sort(table, {{column, Order.Ascending}}), newColumn, 1, 1),

    // Group the fields by [Manager]
    #"Grouped Rows" = Table.Group(#"Merged DeptInfo", {"Manager.1"}, {{"Data", each _, type table}}),

    // Function calling 'AddRanking' to insert the incrementing index number
    Transformed = Table.TransformColumns(#"Grouped Rows", {{"Data", each AddRanking(_, "Merged", "Rank")}}),

    // Expands the table that was created by #"Grouped Rows"
    #"Expand Data" = Table.ExpandTableColumn(Transformed, "Data", {"Merged", "Rank"}, {"Merged", "Rank"}),

    // This is the copy -> paste -> transpose magic that I wanted to automate;
    #"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", "Merged"),
    
    // Extra step to get two columns back - leaves 'Manager' 'Email' 'ColumnIndex x'...
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Pivoted Column", "Manager.1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Manager", "Email"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Manager", type text}, {"Email", type text}})
    
in
    #"Changed Type"

The real question of my post

How can the code be improved next time without the extra step of merging [Manager] & [Email] then splitting them? Let's say that the next project requires [Phone] [Address] to be in the output, is there a way to clean up the code?

The final output looks like the below image. Each record will get one email with some IF MERGEFIELD code in MSWord listing each department that they manage.