2.2.1工具篇-Power Query——Power Query从入门到精通

“我花了80%的时间清洗数据，只有20%的时间分析数据。”——这曾是数据分析师的日常写照。直到Power Query的出现，彻底改变了这一工作模式。

一、为什么Power Query是数据分析必备技能？

在正式开始学习之前，让我们先了解Power Query为什么值得你投入时间学习。

1.1 数据准备的现实挑战

在日常数据分析工作中，我们几乎从未遇到过"完美数据"。典型的数据挑战包括：

• 多源数据：数据分散在Excel、CSV、数据库、Web API等不同来源
• 脏数据问题：包含空白值、错误值、重复值和不一致格式
• 结构问题：需要将宽表转换为长表，或者合并多个相关表格
• 重复劳动：每月、每周甚至每天都需要执行相同的数据清洗步骤

1.2 Power Query的解决方案

Power Query是微软推出的数据连接和数据准备技术，它通过以下方式解决上述问题：

• 可视化操作：无需编写复杂代码，通过点击界面完成复杂转换
• 可重复流程：一次构建数据处理流程，可随时刷新应用于新数据
• 强大集成：深度集成于Excel和Power BI，学习一次，多处应用
• 性能优化：可处理百万行级别数据，远超传统Excel公式能力

二、Power Query界面全解析

让我们先熟悉Power Query的编辑环境，这是后续所有操作的基础。

2.1 打开Power Query编辑器

在Excel中：

1. 选择"数据"选项卡
2. 点击"获取数据"
3. 选择数据源（如"从工作簿"）
4. 选择具体文件和数据表
5. 点击"转换数据"打开Power Query编辑器

2.2 编辑器界面组成

Power Query编辑器分为以下几个关键区域：

• 功能区：包含所有数据转换命令的上方菜单栏
• 查询列表：左侧显示当前工作簿中的所有查询
• 数据预览：中央区域显示当前查询的数据预览
• 属性面板：右侧显示查询属性和应用步骤
• 公式栏：可选显示，展示当前步骤的M代码
  

三、从实战开始：销售数据分析案例

让我们通过一个实际案例，逐步掌握Power Query的核心功能。

3.1 案例背景

假设你是一家零售公司的数据分析师，收到了一份销售数据，存在以下问题：

1. 数据分布在多个工作表中
2. 日期格式不一致（有的为"2023-01-01"，有的为"2023年1月1日"）
3. 产品价格包含货币符号（如"￥100"）
4. 存在空白行和测试数据
5. 需要计算每个订单的总金额

3.2 数据获取与初步处理

步骤1：连接数据源

在Excel中：

1. 点击"数据" > “获取数据” > “从文件” > “从工作簿”
2. 选择你的Excel文件
3. 在导航器中选择需要的工作表
4. 点击"转换数据"而不是"加载"

步骤2：提升标题

经常数据的第一行是列名，但Power Query可能将其识别为数据。解决方法：

1. 在编辑器中，选择"转换"选项卡
2. 点击"将第一行用作标题"

步骤3：更改数据类型

正确的数据类型至关重要：

1. 点击列标题旁边的数据类型图标
2. 选择合适的数据类型（文本、整数、小数、日期等）
3. 或者选择多列，右键选择"更改类型"

// 对应的M语言代码 = Table.TransformColumnTypes(源,{{"OrderID", Int64.Type}, {"OrderDate", type date}})

3.3 高级数据清洗技巧

处理日期格式不一致

1. 选择日期列
2. 右键单击选择"更改类型" > “使用区域设置”
3. 选择"日期"类型和适当的区域设置（如"中文(中国)"）

提取和转换文本值

对于包含"￥100"的价格列：

1. 选择该列
2. 点击"转换"选项卡 > “提取” > “文本之前的分隔符”
3. 输入分隔符"￥"
4. 将结果列更改为小数类型

// 提取数字值的M代码 = Table.TransformColumns(已更改的类型, {{"Price", each Text.AfterDelimiter(_, "￥"), type number}})

填充向下和向上

处理空白单元格：

1. 选择有空白值的列
2. 点击"转换" > “填充” > “向下"或"向上”
3. 这将用上面或下面的值填充空白

条件列：实现复杂逻辑

添加一列标识大额订单：

1. 点击"添加列" > “条件列”
2. 设置条件：如果"销售额"大于1000则"大额"，否则"正常"
3. 或者使用自定义公式模式

// 条件列的M代码 = Table.AddColumn(已更改的类型, "OrderSize", each if [Sales] > 1000 then "Large" else "Normal")

3.4 数据整合与转换

合并多个查询

当数据分布在多个工作表时：

1. 首先为每个工作表创建独立查询
2. 确保所有查询结构相同（列名和数据类型一致）
3. 新建一个空白查询
4. 使用Table.Combine({查询1, 查询2})函数合并

逆透视：从宽表到长表

这是Power Query中最强大且难以理解的功能之一：

1. 选择需要保持不变的列（如ID、日期等）
2. 按住Ctrl选择需要逆透视的多个列
3. 右键单击选择"逆透视列"
4. 结果将生成两个新列：“属性”（原列名）和"值"（原单元格值）

// 逆透视的M代码 = Table.UnpivotOtherColumns(已更改的类型, {"ID", "Date"}, "Attribute", "Value")

分组与聚合

像SQL的GROUP BY一样工作：

1. 选择"转换"选项卡 > “分组依据”
2. 选择要分组的列（如"产品类别"）
3. 添加新列名和聚合操作（如"总销售额"求和）

// 分组聚合的M代码 = Table.Group(已更改的类型, {"Category"}, {{"TotalSales", each List.Sum([Sales]), type number}})

四、重点实战：读取多个文件的高级技巧

在实际工作中，我们经常需要处理分布在多个文件中的数据。以下是两种最常见场景的详细解决方案。

4.1 读取包含多个Excel文件的文件夹

这是处理月度报表、分部门数据等场景的必备技能。

操作步骤：

1. 准备文件：确保所有Excel文件放在同一文件夹中，且结构相同（列名和顺序一致）
2. 获取数据：
   • 在Excel中：数据 → 获取数据 → 从文件 → 从文件夹
   • 选择包含所有Excel文件的文件夹路径
3. 组合文件：
   • 点击"组合"按钮旁边的下拉箭头
   • 选择"合并和转换数据"
4. 选择示例文件：Power Query会提示选择一个文件作为示例
5. 选择工作表：在导航器中选择正确的工作表
6. 调整参数（可选）：
   • 可以设置是否包含文件名作为新列
   • 可以设置是否包含文件路径信息

// 读取多个Excel文件的完整M代码 let Source = Folder.Files("C:\你的文件夹路径"), FilteredHiddenFiles = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), ExcelFiles = Table.SelectRows(FilteredHiddenFiles, each Text.Lower([Extension]) = ".xlsx"), ImportExcel = Table.AddColumn(ExcelFiles, "ExcelData", each Excel.Workbook([Content], true)), ExpandedData = Table.ExpandTableColumn(ImportExcel, "ExcelData", {"Name", "Data", "Item", "Kind", "Hidden"}, {"WorkbookName", "Data", "Item", "Kind", "Hidden"}), FilterSheets = Table.SelectRows(ExpandedData, each [Kind] = "Sheet"), SelectData = Table.SelectColumns(FilterSheets,{"Name", "WorkbookName", "Data"}), ExpandData = Table.ExpandTableColumn(SelectData, "Data", Table.ColumnNames(SelectData[Data]{0})), SetTypes = Table.TransformColumnTypes(ExpandData,{{"Sales", type number}, {"Date", type date}}) in SetTypes

重要注意事项：

• 所有Excel文件的结构必须相同（列名和顺序）
• 文件扩展名可以是.xlsx或.xls，需要在代码中相应调整
• 使用Excel.Workbook([Content], true)中的第二个参数true表示第一行包含标题，最后结果只会留有一行标题
  

4.2 读取包含多个CSV文件的文件夹

处理日志文件、传感器数据、导出数据等场景非常常见。

操作步骤：

1. 准备文件：确保所有CSV文件在同一文件夹，使用相同的分隔符和编码
2. 获取数据：
   • 数据 → 获取数据 → 从文件 → 从文件夹
   • 选择包含CSV文件的文件夹
3. 组合文件：
   • 点击"组合"按钮 → “合并和加载”
   • 或者选择"合并和转换数据"进行进一步处理
4. 配置CSV导入选项：
   • 可以指定分隔符（逗号、分号、制表符等）
   • 可以设置编码格式（UTF-8、ANSI等）
   • 可以处理包含标题行的情况

// 读取多个CSV文件的完整M代码 let Source = Folder.Files("C:\你的CSV文件夹路径"), CsvFiles = Table.SelectRows(Source, each Text.Lower([Extension]) = ".csv"), ImportCsv = Table.AddColumn(CsvFiles, "CsvData", each Csv.Document([Content], [Delimiter=",", Encoding=TextEncoding.Utf8, QuoteStyle=QuoteStyle.Csv])), ExpandedData = Table.ExpandTableColumn(ImportCsv, "CsvData", Table.ColumnNames(ImportCsv[CsvData]{0})), PromoteHeaders = Table.PromoteHeaders(ExpandedData, [PromoteAllScalars=true]), SetTypes = Table.TransformColumnTypes(PromoteHeaders, {{"Date", type date}, {"Amount", type number}, {"ProductID", type text}}), AddFileName = Table.AddColumn(SetTypes, "FileName", each [Name]) in AddFileName

高级配置选项：

// CSV导入的详细参数配置 Csv.Document( contents, [ Delimiter=",", // 分隔符：",", ";", "\t"等 Columns=5, // 指定列数（可选） Encoding=TextEncoding.Utf8, // 编码格式 QuoteStyle=QuoteStyle.Csv, // 引号处理方式 ExtraValues=ExtraValues.Ignore // 处理额外值的方式 ] )

4.3 处理常见问题与错误

问题1：文件结构不一致
解决方案：先统一文件结构，或使用try表达式处理异常

// 使用try处理可能缺失的列 = Table.AddColumn(Source, "CustomColumn", each try [ExistingColumn] otherwise try [AlternativeColumn] otherwise "Default Value")

问题2：文件编码问题
解决方案：指定正确的编码格式

// 尝试不同编码格式 = Table.AddColumn(Source, "Data", each try Csv.Document([Content], Encoding=TextEncoding.Utf8) otherwise try Csv.Document([Content], Encoding=TextEncoding.Windows) otherwise error "无法解析文件")

问题3：性能优化
解决方案：尽早筛选和删除不必要的列

// 优化性能的最佳实践 let Source = Folder.Files("C:\路径"), FilterFiles = Table.SelectRows(Source, each [Extension] = ".csv"), // 尽早筛选 Import = Table.AddColumn(FilterFiles, "Data", each Csv.Document([Content])), RemoveColumns = Table.SelectColumns(Import,{"Data"}), // 删除不必要的列 Expand = Table.ExpandTableColumn(RemoveColumns, "Data", {"Column1", "Column2"}), // 只扩展需要的列 FilterRows = Table.SelectRows(Expand, each [Column1] <> null) // 筛选行 in FilterRows

五、M语言入门：超越界面操作

虽然Power Query提供了可视化界面，但了解背后的M语言能让你突破界面限制。

5.1 M语言基础概念

M语言是Power Query使用的函数式语言，基本结构如下：

let 步骤1 = 表达式1, 步骤2 = 表达式2, ... 最终步骤 = 表达式N in 最终步骤

5.2 常用M函数示例

文本处理函数

// 合并文本 = Text.Combine({"Hello", "World"}, " ") // 返回 "Hello World" // 提取子文本 = Text.Range("PowerQuery", 5, 5) // 返回 "Query" // 替换文本 = Text.Replace("2023年01月", "年", "-") // 返回 "2023-01月"

日期与时间函数

// 获取当前日期 = DateTime.LocalNow() // 提取日期部分 = DateTime.Date(DateTime.LocalNow()) // 计算日期差 = Duration.Days(#date(2023,12,31) - #date(2023,1,1)) // 返回 364

列表与表操作

// 创建列表 = {1, 2, 3, 4, 5} // 列表转换 = List.Transform({1, 2, 3, 4, 5}, each _ * 2) // 返回 {2, 4, 6, 8, 10} // 筛选表 = Table.SelectRows(源, each [Sales] > 1000)

六、高级技巧与最佳实践

6.1 参数化查询

创建可重复使用的参数，使查询更加灵活：

1. 在Power Query编辑器中，点击"管理参数" > “新建参数”
2. 设置参数名称、类型和默认值
3. 在查询中引用参数，如筛选日期大于参数值

// 引用参数的示例 = Table.SelectRows(源, each [OrderDate] > #date(ParameterYear, ParameterMonth, ParameterDay))

6.2 错误处理

确保查询不会因为个别错误值而中断：

// 使用try表达式处理错误 = Table.AddColumn(源, "SafeDivision", each try [Numerator] / [Denominator] otherwise 0)

6.3 性能优化技巧

1. 尽早筛选：在流程开始时使用Table.SelectRows筛选掉不需要的行
2. 选择必要列：使用Table.SelectColumns移除不需要的列
3. 避免重复计算：合理安排步骤顺序，减少中间计算
4. 查询折叠：当连接数据库时，确保操作能"下推"到数据库执行

七、Power Query在Power BI中的进阶应用

在Power BI中，Power Query的能力得到进一步扩展：

7.1 直接连接企业数据库

1. 支持SQL Server、Oracle、MySQL等主流数据库
2. 可编写原生SQL查询或使用可视化查询生成器
3. 支持参数化数据库查询

7.2 更强大的数据处理能力

1. 处理更大数据量（百万行以上）
2. 支持直接调用R和Python脚本进行高级转换
3. 与Dataflows集成，实现企业级数据准备工作流

7.3 与数据模型深度集成

1. 清洗后的数据可直接加载到数据模型
2. 支持创建层次结构、计算列和度量值
3. 为后续的DAX分析和可视化奠定基础

八、学习路径与资源推荐

8.1 循序渐进的学习路径

1. 初级阶段（1-2周）：掌握基本界面操作和常用转换

   • 数据获取与基本清洗
   • 数据类型转换与错误处理
   • 添加列与条件列

2. 中级阶段（2-4周）：掌握数据整合与高级转换

   • 合并与追加查询
   • 逆透视与分组
   • 参数化查询

3. 高级阶段（1-2月）：掌握M语言与性能优化

   • M语言核心语法
   • 自定义函数编写
   • 查询性能分析与优化

8.2 推荐学习资源

1. 官方文档：Power Query文档，M语言参考
2. 专业书籍：《M is for Data Monkey》《Power Query权威指南》
3. 在线课程：微软官方学习路径
4. 社区支持：Power BI社区、Stack Overflow的Power Query标签

结语：从数据清洗工到数据分析师

Power Query不仅仅是一个工具，它代表了一种思维方式的转变——从手动、重复的数据准备工作中解放出来，将更多时间投入到真正的数据分析和洞察发现中。

通过本文学到的技能，你可以：

1. 将数据准备时间从几小时缩短到几分钟
2. 构建可重复使用的数据流程，一劳永逸
3. 处理更复杂、更庞大的数据集
4. 为高级分析和可视化奠定坚实基础

记住， mastery（精通）来自于practice（实践）。选择你当前的数据挑战，开始应用Power Query解决它吧！