为一列单元格中的每个分隔值展开表格行 [英] Expand Table Rows for Each Delimited Value in One Column's Cells

问题描述

我是一名 Excel VBA 程序员，最近发现了 Power Query 和 Power BI.

I'm an Excel VBA programmer who's recently discovered Power Query and Power BI.

通常，我会收到更改以下内容的请求:

Often, I will receive requests to change something like:

变成一种看起来像这样的非旋转版本:

Into a sort of un-pivoted version that looks like:

这完全可以在 VBA 中完成，但是观看几个 Power Query 视频(现在订购了三本书！)似乎在 Power Query 中执行会更有效率.

This can absolutely be done in VBA, but watching several Power Query videos (and now ordered three books!) It seems it would be much more efficient to do it in Power Query.

我得到了总体思路(例如，从分隔列表创建一个表)，但不知道实际步骤的语言.我真的很想学习这些新工具.

I get the over-all idea (create a table from the delimited list, for one) but don't know the language of the actual steps. And I really want to learn these new tools.

我可以买一个元音吗?给朋友打电话?

Can I buy a vowel? Phone a friend?

推荐答案

1. 使用 From Table 将表导入 Power Query
2. 单击第二列，然后从功能区中选择拆分列 | 按分隔符".选择逗号作为分隔符，然后单击确定.
3. 右键单击第一列并选择Unpivot Other Columns".
4. 单击属性"列，然后单击功能区中的删除列".

如果你进入高级编辑器，你可以看到用来生成这个的代码:

If you go to the Advanced Editor, you can see the code used to generate this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Column2",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"Column2.1", "Column2.2", "Column2.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2.1", type text}, {"Column2.2", type text}, {"Column2.3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

这篇关于为一列单元格中的每个分隔值展开表格行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！