excel - 在复制行的其余部分时拆分​​单元格中的多个数据

Question

我正在尝试将 Pokemon DB 标准化为 1NF，但是，我不确定如何在 Excel 中执行此操作。

我想在“能力”中获取多个数据条目并将它们拆分并复制该行。

原始数据

+----------------+-----------+---------------+-----------------------------+
| pokedex_number |   name    | classfication |          abilities          |
+----------------+-----------+---------------+-----------------------------+
|              1 | Bulbasaur | Seed Pokemon  | ['Overgrow', 'Chlorophyll'] |
|              2 | Ivysaur   | Seed Pokemon  | ['Overgrow', 'Chlorophyll'] |
|              3 | Venusaur  | Seed Pokemon  | ['Overgrow', 'Chlorophyll'] |
+----------------+-----------+---------------+-----------------------------+

所需数据

+----------------+-----------+---------------+-------------+
| pokedex_number |   name    | classfication |  abilities  |
+----------------+-----------+---------------+-------------+
|              1 | Bulbasaur | Seed Pokemon  | Overgrow    |
|              1 | Bulbasaur | Seed Pokemon  | Chlorophyll |
|              2 | Ivysaur   | Seed Pokemon  | Overgrow    |
|              2 | Ivysaur   | Seed Pokemon  | Chlorophyll |
|              3 | Venusaur  | Seed Pokemon  | Overgrow    |
|              3 | Venusaur  | Seed Pokemon  | Chlorophyll |
+----------------+-----------+---------------+-------------+

标签： exceldatabaserelational-databasedatabase-normalization

Answer 1

使用Power Query(aka Get&Transform) 将能力列拆分为行 然后删除无关字符[]'

这一切都可以通过 PQ GUI 完成。

M-代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"[","",Replacer.ReplaceText,{"abilities"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","'","",Replacer.ReplaceText,{"abilities"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","]","",Replacer.ReplaceText,{"abilities"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value2", {{"abilities", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "abilities"),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"abilities", type text}})
in
    #"Changed Type"

结果