简介《Power Query入门手册》是一份面向Excel报表自动化初学者的PDF电子资料系统讲解Power QueryPQ中数据导入、清洗、转换与整合的核心流程帮助长期依赖复制粘贴处理数据的用户建立可复用的自动化工作流。内容从入门案例切入逐步拆解获取文本文件、更改数据类型、连接Excel工作簿、数据库与网页等外部数据源再到通过M函数实现自定义导入同时覆盖修改与删除应用步骤、添加索引列和自定义列、逆透视与分组依据、追加查询、仅创建连接、单条件与多条件合并查询及模糊查询等进阶操作几乎涵盖日常数据预处理的主要环节。每个功能点均配有操作步骤与界面示意读者可跟随Power Query编辑器边学边练直观理解列类型转换、条件列设置、模糊匹配等数据变换逻辑。手册末尾的“数据清洗十招”集中演示了按分隔符拆分列、提升标题、删除错误值、筛选剔除行、合并列等实用技巧便于在数据预处理环节直接套用。资源为单份PDF文件约3.13MB轻量易读目前已有4554人学习下载适合需要系统掌握Power Query基础与进阶用法、希望提升Excel数据处理效率的用户。1. 多少人把 Power Query 用成了“高级筛选”打开 Excel点几下“数据 来自表格/区域”把同一文件夹里几十张报表合并成一张总表再顺手把乱码日期、文本型数字清洗干净——这就是 Power Query 最常见的价值。很多人以为它像 VBA 录制宏一样“点几下生成代码”实际用过才发现Power Query 的录制更像是在搭积木每一步操作生成一步 M 语言公式查询面板里所有步骤都能回退、改名、插入这才是它真正比传统 Excel 操作强的点——你的清洗过程不是一次性的而是变成了一套可以反复执行的“数据整理流水线”。这篇笔记就是给想系统入门 Power Query 的从业者写的。不管你是财务、运营还是数据分析师每天和 Excel 表格打交道都有必要搞懂这套工具。我会把查询面板怎么用、M 语言怎么写、常见的坑怎么绕开一条条拆开讲。读完你不仅能复现合并、清洗、计算这类高频场景还能在团队里把这套流程传给下一个人。2. 查询面板真正入门的第一道门槛Power Query 的界面第一眼看上去很朴素左边一个查询列表中间一个数据预览区右边一个“查询设置”窗格。但对新手来说最陌生的不是按钮而是它“每一步都留下痕迹”的工作方式。你每点一次右键、每改一次列名Power Query 就把这步操作记成一条“已应用步骤”将来数据源更新了刷新一下这些步骤会按顺序再跑一遍。这个机制就是 Power Query 的“后悔药”和“黑匣子”二合一——既能回退又容易看不懂。2.1 入口选择从表格区域进还是从文件夹进最常见的入口有两个。一是选中 Excel 里已经整理好的区域用“数据 来自表格/区域”把这块区域送进 Power Query二是点“数据 获取数据 来自文件 从文件夹”一次性把某个目录下所有同构的 Excel 文件全部加载进来。从表格/区域进入时有一个很多人忽略的细节数据源一定要先转换成“表格”快捷键 CtrlT否则 Power Query 只认你选中的那一块固定范围。以后在原始工作表里新增了一行刷新时新行根本不会进来因为它还在傻傻地等在第 99 行那里。从文件夹进入时Power Query 会自动生成一个包含文件名的列还会多出一些元数据列。这里的坑是文件夹里哪怕有一个临时文件损坏刷新就会整条失败。我一般会在进入前先在文件夹里放一个只读的示例文件把结构固定住再让它去读其他文件——相当于先立一个“模板”后续文件都按这个结构对齐。2.2 右键菜单删列、逆透视、替换值全在这里新手最容易忽视的是列标题上的右键菜单。删除列、重命名列、替换值、逆透视列、分组依据、数据类型更改这些高频操作全在右键菜单里。其中“逆透视”是 Power Query 最有价值的一个操作它能把宽表转成长表——就像把一排月份列折叠成“月份 数值”两列。很多数据透视表做不了的清洗工作靠逆透视一下子就能解决。举个例子。原始表长这样城市1月2月3月上海100120140选中 1月、2月、3月三列右键 逆透视其他列瞬间变成城市属性值上海1月100上海2月120上海3月140这才是“可持续整理的表格”后面做筛选、透视图、时间序列分析都方便得多。右键菜单里的“替换值”也很常用尤其是处理脏数据里常见的“空格”和“null”。把不可见字符替换成空值比在 Excel 里用查找替换更安全因为 Power Query 里每步操作都能撤销不会误伤其他单元格。2.3 已应用步骤看懂步骤窗口才算真会入门打开“查询设置”窗格你会看到一列“已应用步骤”从“源”开始之后是“导航”、“更改类型”等每一步操作。这个窗口就是你的整理过程日志。任何一步出了问题点一下那一步中间的数据预览就会显示出那个时点的数据形态这时候你在右侧改点什么后续步骤会自动重算。关键技巧步骤可以重命名。双击任意一步的名字把它改成“删除重复项_2024版”这样的名字比默认的“删除重复项1”“删除重复项2”更容易理解。还有一个小习惯我用了一整年每做完一个阶段性的清洗动作就会在表格里添加一步自定义列写一个阶段注释比如确保“订单号”列无空值后再进入下一步合并。提示M 语言里没有“撤销”按钮。但因为有步骤窗格你可以把出错的那一步直接删除或者把它后面的步骤暂时停用相当于手动实现了撤销。3. M 语言 15 分钟入门能写函数的才算真入门Power Query 的界面操作覆盖了 80% 的日常需求但剩下 20% 必须手写 M 语言。M 语言的全称是 Power Query Formula Language它不是宏也不是 VBA而是一种函数式语言——把每一步操作当成一个函数把上一次的结果传入下一次。理解了这一点你就理解了整个 Power Query 的底层逻辑。3.1 let 与 inM 语言的骨架结构任何一段 M 代码都以let开头以in结尾。let之后是不断用变量名定义中间结果的过程每一行都可以引用前面定义过的变量。这个设计让代码天然可读你不需要把一大串操作嵌套到括号里而是像写步骤清单一样一步步列出。let 源 Excel.CurrentWorkbook(){[Name订单表]}[Content], 删除空行 Table.SelectRows(源, each [订单号] null and [订单号] ), 更改类型 Table.TransformColumnTypes(删除空行, {{金额, Currency.Type}}), 汇总 Table.Group(更改类型, {城市}, {{总金额, each List.Sum([金额]), type number}}) in 汇总这段代码做了四件事从当前工作簿取表、删除订单号为空的行、把金额列设为货币类型、按城市汇总。逻辑说明源是一个表对象后面每一步都基于上一步的结果做变换in后面放最终导出的对象。参数说明Table.SelectRows的第二个参数用each开启行级上下文_在each中代表当前行可以用[列名]取值Table.Group中{城市}是分组键总金额是新建的列名each List.Sum([金额])是聚合逻辑type number指定结果列类型。3.2 each 与下划线理解行级上下文each是 M 语言里最容易摔跤的语法之一。它本质上是(_) ...的简写也就是说each [金额]等价于(_) _[金额]。在你的清洗过程中凡是遇到“针对每一行做判断”的场景都要用到each。最常见的搭配是Table.SelectRows和Table.AddColumn。let 源 Excel.CurrentWorkbook(){[Name订单表]}[Content], 添加列 Table.AddColumn(源, 是否大单, each if [金额] 1000 then 是 else 否) in 添加列参数说明Table.AddColumn第三个参数可以是固定值、一个函数或者一个字段的选择。这里用each生成一个布尔表达式条件为真返回“是”否则返回“否”。注意if ... then ... else在 M 语言里不是函数而是表达式所以它必须有返回值不能省略else。3.3 高频函数组合Table.SelectRows Table.TransformColumnTypes真正高频的组合拳是先筛选再改类型再补列最后分组。新手容易犯的错是顺序颠倒——先改了类型再去筛选结果日期列里混入文本筛选时报错。推荐顺序是先处理行删空、去重、筛选再处理列改名、改类型、加条件列最后做聚合。let 源 Excel.CurrentWorkbook(){[Name销售明细]}[Content], 筛选 Table.SelectRows(源, each DateTime.Year([下单时间]) 2024), 去重 Table.Distinct(筛选, {订单号}), 改类型 Table.TransformColumnTypes(去重, {{金额, Int64.Type}, {下单时间, type datetime}}), 分组 Table.Group(改类型, {城市}, {{总金额, each List.Sum([金额]), type number}}) in 分组参数说明Table.Distinct的第二个参数是可选的传了{订单号}就表示只按这一列判断重复DateTime.Year直接提取日期列里的年份免去先拆分年份再筛选的麻烦。这段代码的完整语义是“找出 2024 年的所有订单按订单号去重再把金额设为整数类型最后按城市求总金额。”每一步都依赖上一步的表格结构所以才叫步骤式编程。3.4 用 List.Accumulate 写循环告别复制粘贴如果你做过多个字段的标准化清洗一定会遇到这种场景有十列都需要把空字符串替换为空值。用界面操作要右键十次而用List.Accumulate一条公式解决。let 源 Excel.CurrentWorkbook(){[Name原始数据]}[Content], 列名列表 Table.ColumnNames(源), 清洗 List.Accumulate( 列名列表, 源, (state, current) Table.ReplaceValue(state, , null, Replacer.ReplaceValue, {current}) ) in 清洗参数说明List.Accumulate是 M 语言里的循环结构第一个参数是要遍历的列表第二个参数是初始值这里就是原始表第三个参数是一个双参数函数state表示上一步处理后的表格current表示当前列名。Table.ReplaceValue接收第五个参数{current}意思是只替换这一列。这个写法的高级之处在于把列名列表动态取出来以后源表新增了列代码不需要改。注意List.Accumulate的循环体能不用就不多用因为 M 语言本身不擅长逐行循环数据量大时会明显变慢。能用Table.TransformColumns批量处理就别手写循环。4. 三个实战案例合并多表、条件列、同比计算案例是检验入门程度的唯一标准。这节我选三个出现频率最高的场景每个都能直接抄走改一改就用。场景一解决文件合并场景二解决逻辑打标场景三解决环比同比。这三个场景覆盖了 Power Query 80% 的日常工作内容。4.1 把同一文件夹下几十个 Excel 合并成一张总表财务、运营岗位最痛的需求每个月月底把各个部门发上来的报表合并成一张表。传统做法是复制粘贴遇到列数不一样、表头有合并单元格就当场翻车。用 Power Query 从文件夹加载配合Table.Combine合并动作变成一次刷新的事情。操作路径数据 获取数据 从文件夹选择目标文件夹后Power Query 会列出所有文件。点“合并”下拉按钮选择“合并和转换数据”PQ 会给你一个文件预览界面选中示例文件里正确的工作表Sheet确认列头无误后确定。这时生成的代码框架里Power Query 用Folder.Files拿到文件列表再通过File.Contents读取每个文件的内容。如果你发现有的文件读不出来常见做法是在进入 PQ 后加一步Table.SelectRows把文件名里包含“临时”字样的行过滤掉避免干扰。let 源 Folder.Files(C:\每月报表), 过滤文件 Table.SelectRows(源, each Text.Contains([Name], .xlsx) and not Text.Contains([Name], ~$)), 读取内容 Table.AddColumn(过滤文件, 自定义, each Excel.Workbook(File.Contents([Full Path]), true)), 展开 Table.ExpandTableColumn(读取内容, 自定义, {Data}, {Data}), 提取数据 Table.ExpandTableColumn(展开, Data, Table.ColumnNames(展开[Data]{0})) in 提取数据逻辑说明Excel.Workbook的第二个参数true表示只读取前几行以推断列名能显著加速大文件读取。展开[Data]{0}是取第一行数据作为列名模板这样即使不同文件的列顺序略有差异也能按列名对齐而不是按位置对齐。参数说明Text.Contains是大小写敏感函数文件名里大写.XLSX会匹配失败。如果你要兼容两种后缀可以写Text.Upper([Name])统一转大写再判断。4.2 用条件列做客户分层替代层层嵌套 IF业务打标往往要用到多条件判断比如“金额大于 5000 且时长大于 60 分钟”算高价值客户。Excel 里的 IF 嵌套一旦超过三层就让人头皮发麻而 Power Query 的Table.AddColumn可以直接用if表达式组合多个条件可读性比 Excel 公式好很多。let 源 Excel.CurrentWorkbook(){[Name客户明细]}[Content], 分层 Table.AddColumn(源, 客户分层, each if [总金额] 5000 and [平均时长] 60 then 高价值 else if [总金额] 2000 then 中价值 else 普通 ) in 分层逻辑说明each里的if ... else if ... else从上到下依次判断遇到第一个为真的条件就返回对应结果。这里最需要注意and与or的优先级——and优先于or所以条件较长时建议用括号把每组判断包起来避免逻辑读错。参数说明Table.AddColumn的特性是新增列不会改变原有列的顺序和类型所以这个分类维度加完后后续再做透视、图表都互不干扰。4.3 算同比先加索引再引用上一行在 Power Query 里算环比本期对比上一期不能直接在表里“下拉公式”。因为表格里的每一行都是独立的行上下文无法直接引用上一行的值。常见做法是给表加一个索引列然后通过索引号去查找上一行的值。let 源 Excel.CurrentWorkbook(){[Name月度业绩]}[Content], 排序 Table.Sort(源, {{月份, Order.Ascending}}), 加索引 Table.AddIndexColumn(排序, 索引, 1, 1, Int64.Type), 上月值 Table.AddColumn(加索引, 上月业绩, each let 上一条 Table.SelectRows(加索引, (x) x[索引] [索引] - 1) in if Table.IsEmpty(上一条) then null else 上一条{0}[业绩] ), 环比 Table.AddColumn(上月值, 环比, each if [上月业绩] null then null else ([业绩] - [上月业绩]) / [上月业绩] ) in 环比逻辑说明Table.AddIndexColumn生成一个从 1 开始的索引列作为每行唯一的标识。Table.SelectRows里用(x) x[索引] [索引] - 1查找前一行注意这里的[索引]在外层each中是指当前行的索引而x是内层查询的每一行。Table.IsEmpty用来处理第一行没有上一行的情况直接返回null而不是报错。参数说明Order.Ascending表示升序排列月份是文本时需确保前缀零存在否则“10月”会排在“2月”前面排序直接出错。提示这个写法在小数据量几千行内验证过没问题但如果数据超过十万行每一行内嵌套一个Table.SelectRows会产生巨大的计算开销。大表建议改用数据库级别的窗口函数比如 SQL Server 的LAG()函数在数据源里算好再导入 Power Query。5. 五个高频避坑记录错误提示背后的真实原因Power Query 的错误提示往往很生硬比如“Expression.Error: The key didnt match any rows”中文环境下就是“找不到该行”。这些报错背后几乎都是同一个逻辑你在一个空表上做了某步操作。下面是五条高频踩坑记录每一条都按“现象 → 原因 → 解决”展开全是血泪经验。5.1 刷新后数据翻倍或丢失问题出在“更改类型”的位置现象Power Query 跑得好好的刷新一遍总行数突然翻倍或者某些行消失了。原因在原始表格里数据是从第 3 行开始填的前面两行是大标题和小标题。Power Query 读取时把第一行作为了列名后面的数据里又有几行被误认为列名于是数据全部错位。解决在“源”步骤后面加一步Table.PromoteHeaders把第一行提升为列名。顺手把列名里的空格、换行符用Table.TransformColumnNames清理掉。let 源 Excel.CurrentWorkbook(){[Name销售表]}[Content], 提升标题 Table.PromoteHeaders(源, [PromoteAllScalars true]), 清洗列名 Table.TransformColumnNames(提升标题, each Text.Clean(Text.Replace(_, , ))) in 清洗列名参数说明PromoteAllScalars true的作用是让每一列都用第一行的值作为列名即使第一行里存在数字或日期也会被强制转换。Text.Clean会去掉不可见字符Text.Replace把空格替掉做到列名标准化。5.2 追加查询时列名顺序不一致导致数据错位现象把两个结构“差不多”的表用“追加查询”合并结果有的列数据对不上比如“金额”列里出现了“城市”的值。原因追加查询Table.Combine是按列名匹配合并的不是按位置。如果两张表里“金额”列在表 A 是第一列、在表 B 是第三列合并结果是两张表各列自动对齐但空值会大量出现看起来就像数据错位。解决追加前先给两个表做一步Table.ReorderColumns把所有列调整成完全一致的顺序或者统一列名。5.3 分列后换行符变成逗号数据出现在同一格里现象用“拆分列 按分隔符”把一个包含换行符的单元格拆开结果没变成多行反而拆成了多列或者同一行里出现了多个值挤在一起。原因Power Query 的分列和 Excel 的分列行为不一样。Excel 分列是按“列宽度”或“分隔符”把内容拆到不同列Power Query 的“按分隔符”拆出来的是新的列而不是新的行。如果你的目标是“把一个单元格里的多行内容拆成多行”正确操作是先“替换值”把换行符替换成一个稀有分隔符比如|||再按这个分隔符拆分成新行。let 源 Excel.CurrentWorkbook(){[Name备注表]}[Content], 替换换行 Table.ReplaceValue(源, #(lf), |||, Replacer.ReplaceText, {备注}), 按分隔符拆分 Table.SplitColumn(替换换行, 备注, Splitter.SplitTextByDelimiter(|||), {备注_1, 备注_2, 备注_3}) in 按分隔符拆分参数说明#(lf)是 M 语言对换行符的转义写法#(cr)回车符同理。Replacer.ReplaceText表示按文本匹配替换。Splitter.SplitTextByDelimiter是一个拆分器函数也可以直接简写成Splitter.SplitTextByDelimiter(|||, null, true)第三个参数true表示允许空段。但要注意分出来后每行会变成多列你还需要之后再逆透视或者分组才能转成干净的长表。5.4 刷新速度越来越慢没看懂“数据集折叠”这个玄学现象查询只有几万行但刷新要几十秒甚至几分钟而且越加步骤越慢。原因Power Query 在读取数据源时存在“查询折叠”Query Folding概念。当你直接连接 SQL Server 这类数据库时PQ 会把筛选、分组这些操作翻译成 SQL 语句交给数据库执行只把结果拉回来。但当你做了“更改本地类型”如把数字改成文本折叠就会中断所有数据必须先全量拉回本地再在本地执行步骤速度自然暴跌。解决尽量把类型转换、筛选提前到 SQL 视图或数据库内部完成如果必须在 PQ 里做把慢步骤和快步骤分开先用Table.Buffer缓存一次结果后面步骤都基于缓存执行。let 源 Sql.Database(服务器地址, 数据库名, [Query SELECT * FROM 销售表 WHERE 年份 2024]), 缓存 Table.Buffer(源), 分组 Table.Group(缓存, {城市}, {{总金额, each List.Sum([金额]), type number}}) in 分组参数说明Sql.Database的第三种写法是直接传 SQL 查询语句这样筛选逻辑直接交给 SQL Server数据量再大也能做到几秒返回。之后的Table.Buffer把数据固定在内存里后续分组、排序都不需要反复回源。但注意Table.Buffer会占用内存几百 MB 的大表慎用否则本地内存会先崩。5.5 明明改了 Excel 原始表刷新后却没变化现象在 Excel 原始工作簿里改了数字、加了行回 Power Query 点“刷新预览”数据纹丝不动。原因Power Query 连接 Excel 工作簿时存在一个“数据源缓存”。如果你用了“从工作簿”导入而不是“从表格/区域”导入系统可能还在读上一次的缓存快照。解决在“数据源设置”里找到对应的连接点“编辑权限”把“隔离级别”改成“总是使用这些设置”或者干脆删掉这个连接重新建一次。从 Excel 文件导入时我一般会把文件放到独立文件夹不放在带宏的工作簿里否则宏触发保存事件时PQ 读取的数据源容易被锁定出现“文件正在使用”的报错。6. 进阶操作让清洗流程变成团队资产当你把单次的数据整理变成了 Power Query 查询就已经领先一半人。但要想让它变成团队资产还得做三件事参数化、自动化、模块化。这三件事做对了你的 Power Query 查询就不再是“你离职就断掉的流水线”而是一个别人也能接手维护的小系统。第一步把清洗过程中所有“可变的东西”抽出来做成参数。比如数据源的文件夹路径、目标年份、金额阈值全部用“管理参数”定义好。点击“主页 管理参数”新建一个参数叫目标年份默认值填 2024然后在 M 代码里用目标年份替代写死的2024。这样下个月到了别人只需要在参数面板里改一个数字整套查询全部生效。不用再翻代码去改三处硬编码。第二步把最终结果加载到 Excel 工作表时只用“表”模式不要用“仅连接”模式。“仅连接”适合中间查询不适合最终交付。加载成表后右键这个表选择“刷新”整个流程重跑一遍如果要每天自动更新可以配合 Excel VBA 的ThisWorkbook.RefreshAll写个事件打开文件时自动刷新或者在任务计划程序里定时调用一个刷新脚本。第三步模块化拆分。我一般会把一个完整方案拆成三个查询基础清洗负责去重、改类型业务计算负责添加条件列、分组聚合最终输出负责排序、选择列、加载。中间查询之间用“引用查询”而不是“复制粘贴表”。在 Power Query 里右键一个查询选“引用”新查询会直接把上一个查询的结果当作数据源。这个设计的好处是你可以单独调试基础清洗而不影响后面的逻辑而且业务计算里如果改了公式最终输出刷新即可生效不用三处同步改。第四步验证结果。每完成一个查询在“筛选行”后面加一步Table.RowCount你可能会觉得多此一举但实际跑数据时行数突变是最常见的翻车现场。我会在最终输出的表旁边放一个单元格写一个ROWS(查询名)的公式刷新后看到行数和上次一致才敢把数据发给业务方。这些年我带团队做报表亲眼见到很多人被 Power Query 的错误提示吓退回到复制粘贴的老路。其实大多数报错就两种一是数据源结构变了二是你在错误步骤上做了不存在的操作。把查询面板里的每一步命名得清清楚楚把参数抽出来把中间查询拆开绝大多数问题在 30 秒内就能定位。希望这篇笔记能帮你在 Power Query 这条路上少踩几个坑把整理数据的活从每天重复的苦力变成一劳永逸的自动化流程。本文还有配套的精品资源点击获取