跳到主要内容

Power Query

问题​

Power Query 是什么？在数据分析中有哪些核心应用？

答案​

什么是 Power Query​

Power Query 是 Excel 和 Power BI 内置的 ETL（Extract-Transform-Load）工具，可以从多种数据源获取数据，进行清洗、转换、合并，然后加载到工作表或数据模型中。

核心优势
  • 操作可录制、可重复：所有步骤记录在查询中，数据更新时一键刷新
  • 不修改原始数据：所有转换在副本上进行
  • 支持大数据量：比公式处理大数据集高效得多

数据获取​

数据源说明
Excel 文件单个/多个工作簿
CSV/TXT自动检测分隔符
数据库SQL Server、MySQL、PostgreSQL
Web 页面自动识别表格
APIJSON/XML 数据
文件夹批量合并同目录多个文件
数据 → 获取数据 → 选择数据源 → 转换数据（Power Query 编辑器）

核心操作​

数据清洗​

操作场景菜单路径
删除行删除空行、错误行、前 N 行主页 → 删除行
替换值统一格式（"男"/"M" → "男"）转换 → 替换值
拆分列姓名拆为姓+名转换 → 拆分列
合并列合并姓+名添加列 → 合并列
更改类型文本/数字/日期转换转换 → 数据类型
填充向下/向上填充合并单元格转换 → 填充
去重删除重复行主页 → 删除重复项
逆透视列宽表变长表转换 → 逆透视列

数据合并​

方式说明对应 SQL
追加查询纵向拼接（行合并）UNION ALL
合并查询横向关联（列合并）JOIN

合并查询的联接类型：

联接类型说明
左外部保留左表全部 + 右表匹配
右外部保留右表全部 + 左表匹配
完全外部保留两表全部
内部只保留两表都匹配的
左反左表中找不到匹配的
右反右表中找不到匹配的

M 语言基础​

Power Query 底层使用 M 语言：

let
    // Step 1: 获取数据
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    
    // Step 2: 更改类型
    TypeChanged = Table.TransformColumnTypes(Source, {
        {"日期", type date},
        {"金额", type number}
    }),
    
    // Step 3: 筛选
    Filtered = Table.SelectRows(TypeChanged, each [金额] > 0),
    
    // Step 4: 添加计算列
    AddedColumn = Table.AddColumn(Filtered, "月份", 
        each Date.Month([日期]), Int64.Type)
in
    AddedColumn

高级功能​

合并文件夹中所有文件​

1. 获取数据 → 从文件夹
2. 选择文件夹 → 合并 → 转换数据
3. Power Query 自动生成合并逻辑

适用场景：每月报表分散在多个 Excel 文件中，一键合并。

参数化查询​

// 创建参数
StartDate = #date(2024, 1, 1)

// 在查询中使用
Filtered = Table.SelectRows(Source, each [日期] >= StartDate)

自定义函数​

// 定义可复用的清洗函数
let
    CleanData = (inputTable as table) =>
    let
        Trimmed = Table.TransformColumns(inputTable, {}, Text.Trim),
        NoEmpty = Table.SelectRows(Trimmed, each not List.IsEmpty(
            List.RemoveMatchingItems(Record.FieldValues(_), {"", null})
        ))
    in
        NoEmpty
in
    CleanData

刷新策略​

场景策略
手动更新数据 → 全部刷新
打开时自动刷新连接属性 → 打开文件时刷新
定时刷新Power BI Service 支持定时刷新
增量刷新Power BI Premium 支持增量加载

常见面试问题​

Q1: Power Query 与公式相比有什么优势？​

答案：

维度Power Query公式
数据量可处理数百万行超过 10 万行明显变慢
可维护性步骤可视化、可编辑公式嵌套难以理解
可复用刷新即可重复执行需要重新编写
数据源支持数据库、API、文件夹仅 Excel 数据
合并数据追加/合并查询VLOOKUP + 手动拼接

Q2: 如何处理"透视表形式"的宽表？​

答案：使用逆透视列（Unpivot）：

原始宽表:
| 产品 | 1月 | 2月 | 3月 |
|------|-----|-----|-----|
| A    | 100 | 200 | 150 |

逆透视后:
| 产品 | 月份 | 销量 |
|------|------|------|
| A    | 1月  | 100  |
| A    | 2月  | 200  |
| A    | 3月  | 150  |

操作：选中需要逆透视的列 → 转换 → 逆透视列

Q3: Power Query 加载数据时很慢怎么优化？​

答案：

  1. 尽早筛选：在靠前的步骤就过滤掉无用数据
  2. 减少列：删除不需要的列减少内存占用
  3. 避免在单元格级操作：用列级转换代替逐行操作
  4. 关闭"加载到工作表"：只加载到数据模型
  5. 数据库查询下推：让筛选条件在数据库端执行

相关链接​