Transpose/Transform Multiple Rows into Single Row

[ChrisMendoza]

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.

[v-yulgu-msft]

Hi @ChrisMendoza,

Why do you need to "merge [Manager] & [Email] then splitting them"? Please show us the original table data.

Regards,

Yuliana Gu

[ChrisMendoza · Accepted solution]

@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:

ManagerName	Email	DeptID	DeptDesc
Duck,Daffy	daffy@duck.com	D10001	Daffy1Acct
Duck,Daffy	daffy@duck.com	D10001	Daffy1Acct
Duck,Daffy	daffy@duck.com	D10001	Daffy1Acct
Duck,Daffy	daffy@duck.com	D10001	Daffy1Acct
Duck,Daffy	daffy@duck.com	D10001	Daffy1Acct
Duck,Daffy	daffy@duck.com	D10001	Daffy1Acct
Duck,Daffy	daffy@duck.com	D10002	Daffy2Acct
Duck,Daffy	daffy@duck.com	D10002	Daffy2Acct
Duck,Daffy	daffy@duck.com	D10002	Daffy2Acct
Mouse,Minnie	minnie@mouse.com	D90001	Minnie1Acct
Mouse,Minnie	minnie@mouse.com	D90002	Minnie2Acct
Mouse,Minnie	minnie@mouse.com	D90002	Minnie2Acct
Mouse,Minnie	minnie@mouse.com	D90002	Minnie2Acct
Mouse,Minnie	minnie@mouse.com	D90002	Minnie2Acct
Mouse,Minnie	minnie@mouse.com	D90002	Minnie2Acct
Mouse,Minnie	minnie@mouse.com	D90002	Minnie2Acct
Mouse,Mickey	mickey@mouse.com	D30001	Mickey1Acct
Mouse,Mickey	mickey@mouse.com	D30001	Mickey1Acct
Mouse,Mickey	mickey@mouse.com	D30001	Mickey1Acct
Mouse,Mickey	mickey@mouse.com	D30001	Mickey1Acct
Mouse,Mickey	mickey@mouse.com	D30002	Mickey2Acct
Mouse,Mickey	mickey@mouse.com	D30002	Mickey2Acct
Mouse,Mickey	mickey@mouse.com	D30002	Mickey2Acct
Mouse,Mickey	mickey@mouse.com	D30002	Mickey2Acct
Mouse,Mickey	mickey@mouse.com	D30002	Mickey2Acct
Mouse,Mickey	mickey@mouse.com	D30003	Mickey3Acct
McDuck,Scrooge	scrouge@mcduck.com	D60001	Scrouge1Acct

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.

ManagerName	Email	1	2	3
Daffy Duck	daffy@duck.com	D10001 - Daffy1Acct	D10002 - Daffy2Acct
Mickey Mouse	mickey@mouse.com	D30001 - Mickey1Acct	D30002 - Mickey2Acct	D30003 - Mickey3Acct
Minnie Mouse	minnie@mouse.com	D90001 - Minnie1Acct	D90002 - Minnie2Acct
Scrooge McDuck	scrouge@mcduck.com	D60001 - Scrouge1Acct