excel - Power Query - 根据单元格中电子邮件的出现创建和填充列 Email1、Email2 ...

Question

我有一个像这样的“电子邮件”表:

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

一个人可以有多个电子邮件

我想构建一个这样的“人”表:

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

其中，Emailn 中的 n 是拥有所有用户的最大电子邮件数量的人的电子邮件数量。

如何在 Power Query 中实现这一点？

我知道您可以透视表并执行电子邮件计数、电子邮件最大数量等操作。但我无法弄清楚构建上表的逻辑。

Answer 1

首先在查询编辑器中加载表。然后按(转换选项卡)[用户名]分组。对于操作，选择所有行。像这样:



然后添加自定义列，将每个表的[Mail]列转换为列表。



添加另一个自定义列，使用“|”将每个列表中的值连接到一个字符串值分隔符。



然后用分隔符“|”拆分 [Mail] 列(使用主页选项卡上的拆分列)。最后删除 [mailsTable] 和 [mailsList] 列。

结果(在 Power Query 编辑器中)如下所示:



这是高级编辑器中列出的脚本。

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"