Power Query - Create and fill columns Email1, Email2 ... based on occurrence of email in cells

Question

I have an "emails" table like this:

Username Email
john  john@gmail.com
john  john@stanford.edu
peter peter@gmail.com
...

where one person can have multiple emails

I want to construct a "people" table like this:

Username Email1 Email2 Email3 ...Emailn
john     john@gmail.com john@stanford.edu *null* ... *null*
peter  peter@gmail.com *null* *null* ... *null*
...

Where n in Emailn is the number of emails of the person who has the max number of emails of all users.

How can I achieve this in Power Query?

I understand you can pivot tables and do things like Count of Email, Max of Number of Emails etc. But I can't figure out the logic to build the above table.

Answer 1

First load the table in the Query-editor. Then group by (Transform-tab) [Username]. For the operation select All rows. Like this:

Then add a custom column to convert the [Mail] column of each table into a list.

Add another custom column that concatenates the values in each list to one stringvalue, using the "|" delimiter.

Then split the [Mail] Column by Delimiter "|" (use Split Column on the Home-tab). Finally delete the [mailsTable] and [mailsList] columns.

The result (in the Power Query Editor) looks like this:

And this is the script as listed in the advanced editor.

let
    Source = Excel.CurrentWorkbook(){[Name="table"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Username", type text}, {"Mail", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Username"}, {{"mailsTable", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "mailsList", each Table.Column([mailsTable],"Mail")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Mail", each Text.Combine([mailsList],"|")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom1", "Mail", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Mail.1", "Mail.2", "Mail.3", "Mail.4", "Mail.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Mail.1", type text}, {"Mail.2", type text}, {"Mail.3", type text}, {"Mail.4", type text}, {"Mail.5", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"mailsTable", "mailsList"})
in
    #"Removed Columns"