如何用Power Query为月度销售报告的产品行匹配对应城市列并自动化清洗

可视化界面操作步骤 #

1. 导入数据
   通过「数据」选项卡→「自文本/CSV」导入你的销售报告，导入后会得到一列原始数据（默认列名Column1）。

2. 移除标题行
   选中第一行的Products:，右键选择「删除顶部行」，或在「转换」选项卡点击「删除行」→「删除顶部行」，输入行数1。

3. 标记城市行
   点击「添加列」选项卡→「自定义列」，输入公式：

   not Text.Contains([Column1], "|")
   

   将该列重命名为是否为城市。

4. 提取并填充城市值

   • 再添加一个自定义列，输入公式提取城市名：

     if [是否为城市] then [Column1] else null
     

     重命名为城市。
   • 选中城市列，点击「转换」选项卡→「填充」→「向下填充」，所有产品行将自动匹配上方最近的城市值。

5. 过滤城市行
   点击是否为城市列的筛选按钮，取消勾选True，仅保留产品数据行。

6. 拆分产品与销量
   选中Column1列，点击「转换」选项卡→「拆分列」→「按分隔符」，选择|作为分隔符，拆分为两列后重命名为产品和销量。

7. 整理销量数据类型
   选中销量列，点击「转换」选项卡→「替换值」，将,替换为空；随后将该列数据类型转换为「数值」。

完整M代码参考 #

let
    源 = Csv.Document(File.Contents("你的文件路径"),[Delimiter=";", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    删除顶部行 = Table.Skip(源, 1),
    添加自定义列 = Table.AddColumn(删除顶部行, "是否为城市", each not Text.Contains([Column1], "|")),
    添加自定义列1 = Table.AddColumn(添加自定义列, "城市", each if [是否为城市] then [Column1] else null),
    向下填充 = Table.FillDown(添加自定义列1,{"城市"}),
    筛选行 = Table.SelectRows(向下填充, each ([是否为城市] = false)),
    拆分列 = Table.SplitColumn(筛选行, "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"产品", "销量"}),
    替换值 = Table.ReplaceValue(拆分列,",","",Replacer.ReplaceText,{"销量"}),
    更改类型 = Table.TransformColumnTypes(替换值,{{"产品", type text}, {"销量", Int64.Type}, {"是否为城市", type logical}, {"城市", type text}})
in
    更改类型

内容的提问来源于stack exchange，提问作者Rich F