Power Query动态填充：告别Excel手动下拉，实现自动化数据清洗

1. 动态填充：到底在解决什么问题

我最早接触PowerQuery里的动态填充，是被一个数据报表逼的。当时有一份几千行的库存明细，每天只在部分行标了日期和责任人，其余行全是空白的，但业务上每一条记录都必须归属到最近一次填写的日期和责任人下面。按行的顺序往下找非空值，把上一条的值填进空位，这听起来简单，可一旦放到几千行的真实数据里，用Excel自带的下拉填充容易拖歪，复制粘贴又会撞上合并单元格，最后折腾半天不如老老实实写几行M代码。

在PowerQuery里，这类操作统称动态填充，核心手段是Table.FillDown和Table.FillUp。它的本质是：基于当前行与相邻行的相对位置，用上一个非空值或下一个非空值去填补空白单元格。和Excel里普通填充不同的是，PowerQuery把这个过程变成了可复用的数据清洗步骤，写一次，刷新一次，下次数据来了直接重新执行，不用再手动拖一遍。

这个能力适合谁用？三类人最受益。第一类是经常处理从ERP、业务系统导出的报表的人，那些报表的明细行普遍存在"日期、单据号、部门"等字段大量留空的情况；第二类是做数据合并的人，把多个Sheet、多个文件汇总在一起后，经常发现某些关键列只有部分行有值；第三类是刚开始学习PowerQuery的Excel重度用户，动态填充是最容易理解、也最容易见效的M语言入门动作，一旦搞明白了，后面学分组、学自定义列都会顺手很多。

说白了，动态填充解决的问题，就是"上一行有值，这一行没值，但这两行在业务上属于同一个数据块"。数据块之间靠空行或者分类列隔开，填充的时候不能把上一块的值漏到下一块里，这个"块"的概念，正是动态填充里最容易出错的点，也是后面要重点展开的内容。

2. 核心思路拆解：为什么需要"上一行的值"

2.1 空值填充的本质逻辑

动态填充的底层逻辑说穿了只有一句话：把空单元格视为"继承"状态。在Excel里做下拉填充时，你选中两个有值的单元格，往下拖，Excel会按等差或等值规律延续；PowerQuery里的Table.FillDown干的是同一件事，但它只认两样东西：列的位置和null。只要单元格的值是null，它就会往上找最近一个非null的值填进来，直到遇到下一个非空值再重置方向。

这里有个非常重要的前提：PowerQuery里判断"空"的标准是null，不是你看到的"空白单元格"。使用Excel工作表作为数据源时，空单元格导入后通常会变成null，这是正常的；但如果你用CSV或者从某些数据库导入，空白可能会变成空字符串""。空字符串不是null，Table.FillDown不会碰它。这个问题后面会专门讲到，很多人的填充"没反应"，实际就是卡在这里。

另一种常见场景是"合并单元格导入后只有第一行有值"。Excel工作表里的合并单元格被PowerQuery读取后，并不会自动展开成每行都有值，而是只有左上角那一格有值，其余全是null。很多人在这一步才开始意识到，原来动态填充不是在处理"空缺"，而是在处理"数据被折叠后的展开"。理解了这个本质，后续的分组、填充、展开就不再是死记函数名了。

2.2 填充方向的优先级与判定

Table.FillDown是从上往下填，Table.FillUp是从下往上填。方向不同，业务含义完全不同。向下填充适合的场景：层级表中的上级单位名称、分类汇总表中每组的组长姓名、单据明细中重复出现的店铺名称。向上填充适合的场景：倒序排列的数据中，日期落在表格底部而分类标识在顶部，或者"最后一行汇总备注"需要上溯填充到前面的空行。

方向选择在写代码之前就要定下来，因为一旦填错方向，数据会全部错位，而且PowerQuery不会报错，你只能在预览里肉眼发现。我的经验是：先看业务主键，再看排序逻辑。比如一个流水表按时间升序排列，那么"每笔交易属于哪个店铺"这种字段必然用FillDown；如果是按时间降序排列，那就用FillUp。方向与排序方向相反时，填充结果就是灾难。

还有一个值得留意的点：多列同时填充时，每一列的填充行为是独立的。Table.FillDown(表, {"责任人", "部门"})并不是把"责任人"填充后拿结果去填"部门"，而是两列各自在原有列的基础上向下填充，互不干扰。理解这一点，才不会在写自定义步骤时产生"上一列的结果会带动下一列"的误解。

2.3 动态填充与普通填充的区别

很多人问：直接用鼠标拖不行吗？行，但只局限于你已经打开的那份Excel文件。一旦数据量到几万行，鼠标拖拽的体验和出错的概率都很高；更麻烦的是，每次收到新数据都要重新拖一遍。PowerQuery的动态填充把动作记录成一个查询步骤，下次刷新直接重放，工序稳定，结果可复现，也更不容易把错误的单元格覆盖进去。

普通填充还有一个致命缺陷：它不识别"分组边界"。如果你用Excel下拉填充，它会一直填到没有数据为止，不会因为你中间出现了另一个人的名字就停下来。PowerQuery则可以配合Table.Group按分组字段切块，再在每个分组内部执行填充。这个组合是动态填充的灵魂，也是很多教程里一笔带过但实际最常用的操作。后面我会用一个完整的案例把它讲透。

3. 实操环节：最常见的动态填充写法

3.1 基础写法：Table.FillDown

先看最简单的场景。假设从系统导出的数据长这样：

你在PowerQuery里的操作步骤非常简单：在"添加列"选项卡里选"自定义列"，或者在公式栏里直接写。标准的M代码如下：

let 源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content], 填充日期 = Table.FillDown(源, {"日期"}), 填充部门 = Table.FillDown(填充日期, {"部门"}), 填充负责人 = Table.FillDown(填充部门, {"负责人"}) in 填充负责人

实际执行时，我不建议一行一行写三次填充，那样预览列表会很长。更简洁的写法是把多个列放在同一个列表里：

let 源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content], 填充结果 = Table.FillDown(源, {"日期", "部门", "负责人"}) in 填充结果

这里要注意，Table.FillDown从左到右处理列，但每一列都是独立填充的，不会出现"先填日期，再用填充后的日期作条件去填部门"这种联动效果。如果你需要联动，必须拆成多步，或者用后面提到的自定义列方案。

3.2 进阶写法：分组填充

分组填充解决的是"不同部门的负责人不能混着填"的问题。现实中的数据往往不是干干净净的，可能是多个部门的记录混在一起，中间没有空行隔开。比如A部门的负责人是张三，B部门的负责人是李四，但两个部门的记录交替出现，如果用全局Table.FillDown，张三的名字会被错误地填进B部门的空行里。

正确的做法是先用Table.Group按"部门"列分组，然后在每个分组内部执行向下填充，最后再合并回来。M代码这样写：

let 源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content], 分组填充 = Table.Group(源, {"部门"}, { {"分组数据", each Table.FillDown(_, {"负责人", "日期"})} }), 合并 = Table.Combine(分组填充[分组数据]) in 合并

这里面最关键的一个细节是：Table.Group默认会把分组的键列从结果里单独拎出来作为分组标识，然后在"分组数据"字段里保存整个子表。当你对子表执行Table.FillDown后，用Table.Combine把子表重新拼成一张大表，列会保持在子表里的原始状态，包括"部门"列。这样输出的结果，每个部门只有自己的值在内部填充，不会串到别的部门去。

我在实际项目里用这个模式处理过上万行的销售单据，执行速度在PowerQuery里完全没问题，大概一两秒就结束了。如果数据量更大，还可以在Table.Group里加上Table.Sort，按日期排好序再填充，保证每组内部的填充顺序是可控的。

3.3 跨列填充：同一行的多列联动

有些场景下，同一行中A列空了，但B列有值，你希望用B列的值填A列；或者反过来，B列空了，用A列的值填B列。这已经不叫"填充"了，属于"列合并"的范畴，但很多人同样会搜"动态填充"找到这里。

处理思路有两种。第一种是用Table.FillDown逐列独立操作，完成后把结果合并到新列里；第二种是直接在"添加列"里写一个自定义列公式，用if Value.Is(字段, type null) then 另一列 else 字段这样的逻辑。M代码示例：

let 源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content], 自定义列 = Table.AddColumn(源, "最终值", each if [列A] = null then [列B] else [列A]) in 自定义列

这种写法的好处是逻辑直接、可读性强，而且不会出现"上一行的值跑下来覆盖了当前行的判断"这种问题。它只针对当前行做判断，非常适合处理"同一行内多列互备"的数据。

3.4 条件填充：当某列满足条件时才填充

比分组填充更灵活的是条件填充。比如你希望"只有当状态列等于'待确认'时，才用上一行的值填充；其他情况保持原样"。这个用原生的Table.FillDown做不到，因为FillDown不区分空单元格的前后内容。

解决办法是：先把不需要填充的数据在临时列中"占位"，等填充完成后删除临时列。举个例子：

let 源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content], 加辅助列 = Table.AddColumn(源, "临时值", each if [状态] = "待确认" then null else [负责人]), 填充 = Table.FillDown(加辅助列, {"临时值"}), 还原 = Table.ReplaceValue(填充, each [临时值], each if [状态] = "待确认" then [临时值] else [负责人], Replacer.ReplaceValue, {"负责人"}) in 还原

思路是：先把原始值复制到"临时值"列，但只在需要填充的位置置空；对"临时值"执行FillDown；最后把"临时值"的结果按条件写回原列。这个模式看起来绕，但实际比想象中稳，因为PowerQuery的表操作永远优先于逐行循环思维，能不动用List.Transform的地方就不要动用。

4. 参数选择与逻辑：为什么Table.FillUp不够用

4.1 FillDown与FillUp的参数差异

Table.FillDown和Table.FillUp的函数签名完全一致：Table.FillDown(表, 列名列表)和Table.FillUp(表, 列名列表)，没有任何额外参数。这意味着你能控制的只有"向上还是向下"和"填哪些列"两个维度。更多复杂的业务规则统统需要自己搭辅助列、分组或自定义函数。

很多人会误以为FillUp是FillDown的天然逆操作，只要数据顺序反过来用就行。但在实践里，FillUp更常出现在"倒序排列的数据"或"数值型层级表"中。比如库存盘点表按"货架编号"倒序排列，每一种货架的最后一行写着货架名，它前面的空行都需要向上填充到第一个出现的位置。这时候FillUp就比FillDown少一次排序操作。

4.2 自定义填充函数：用List.Generate实现更复杂逻辑

如果你发现FillDown和FillUp都满足不了需要，比如"以当前行的某个特征为分界，分界之间填充，分界之外不填"，那就需要在M语言里写自定义函数。最常用来做"按位置管填充"的工具是List.Generate，它可以像写循环一样遍历每一行，同时保留上一个状态。

举个例子，你手头有一份订单明细，每个订单的第一行有订单号，后续行都是"继续收货"的明细，但偶尔会有新的订单号出现在中间。你想把订单号填充到所属的所有行里，同时不能误填到下一个订单的行里去：

let 源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content], lst = Table.ToRecords(源), 填充 = List.Generate( () => [i = 0, 当前单号 = null, 结果 = {}], each [i] < List.Count(lst), each [ i = [i] + 1, 当前单号 = if lst{i}[订单号] <> null then lst{i}[订单号] else [当前单号], 结果 = [结果] & {当前单号} ], each [结果] ), 转回表 = Table.FromColumns( Table.ToColumns(源) & {List.Combine(填充)}, Table.ColumnNames(源) & {"填充结果"} ) in 转回表

这段代码的关键在于：当前单号在每次迭代时会检查当前记录的"订单号"，如果有新值就更新，否则沿用上一次的值。这样你在结果集里就得到了一个全新的列，每一行都有正确的订单号，而不必依赖表位置和分组域名。

不过写这样的自定义函数需要谨慎。List.Generate的性能在几万行级别还能扛，但如果你有几十万行，建议还是回到分组填充方案，因为Table.Group是用C语言内部算法实现的，比M语言循环快得多。能用原生函数解决问题，就别硬写循环，这是我在PowerQuery上踩过很多次坑之后得出的结论。

4.3 使用自定义列与递归

除了List.Generate，你还可以在"添加列"里写一个自定义列，利用@符号自己调用上一层，但不建议在PowerQuery里做真正的递归。M语言支持函数内调用函数，但递归层数深了性能极差，也容易把查询搞得难以维护。我见过有人在M里用递归实现"查找上一非空值"，用了List.Accumulate和List.Last绕了好几道弯，最后跑出来的结果还没直接FillDown然后FillUp组合来得快。

这里我的建议很简单：动态填充问题优先考虑组合拳。FillDown+FillUp+Table.Group+ 辅助列，基本能覆盖90%的业务场景。剩下的复杂逻辑，你再考虑用List.Generate或Table.TransformRows做逐行处理。别一上来就上递归，除非你真的需要一个"向上找N行才填充"的函数。

5. 实操现场记录：一个完整的报表清洗案例

5.1 需求描述与数据预览

这里我拿一个真实做过的案例来讲，比抽象的函数说明更接地气。当时我收到的是一份门店库存表的Excel导出文件，结构大致如下：

• 列A：日期，按天递增
• 列B：城市，只有换城市时这一行有值
• 列C：门店，和城市一样，只有每个城市的第一行有值
• 列D：库存数量，每一行都有
• 列E：负责人，和门店并列，只在门店首行出现

数据整体是"城市块"与"门店块"两层嵌套结构。最终目标是：把"城市"和"门店"所在的空行全部填满，得到一张每行都带城市、门店和负责人的完整明细表。

5.2 步骤拆解

第一步，导入Excel数据到PowerQuery编辑器，先确认列类型。日期列会自动识别为datetime，没问题；城市、门店、负责人列可能是text或any，如果出现大量null，也无所谓。

第二步是最关键的一步：先填充"城市"，再填充"门店"，再填充"负责人"。看起来都是FillDown，但顺序不能乱。因为门店块内部也嵌套着负责人，如果不先把城市填充好，直接填充门店，会导致门店块的边界判断失效。操作逻辑是：先按最小粒度填充，再按更大粒度填充，从里往外扩散。

具体M代码如下：

let 源 = Excel.CurrentWorkbook(){[Name="表2"]}[Content], 填门店 = Table.FillDown(源, {"门店", "负责人"}), 填城市 = Table.FillDown(填门店, {"城市"}) in 填城市

这里我故意先填"门店"和"负责人"，再填"城市"。原因在于：Table.FillDown执行时，每一列独立处理，"门店"列不会因为"城市"列还没填而受影响；但反过来，"城市"列如果先填了，后面的门店填充在数据内容上并没有区别。真正影响结果的是列与列之间在业务上是否有嵌套关系。本例中"负责人"必须跟随门店走，所以必须和门店一起填入；而"城市"是一个更大范围的分组，放在最后填是为了让"城市"从每一行看都是正确的上级分组值。

第三步，检查填充后的结果：门店、负责人、城市三列是否还有null。筛选每一列中的null行，理论上应该是0行。如果还有null，说明原始数据中存在"第一行就没有初始值"的记录，那要么是脏数据，要么是业务上的孤立记录，需要单独处理。

第四步，验证每个门店的负责人是否一致。用分组统计看"门店+负责人"组合是否唯一，如果能查出某个门店下面出现了两个不同的负责人，说明原始数据本身有问题，不是填充可以解决的，需要回到源表核对。

5.3 过程中踩过的坑

这个案例里最容易踩的坑有两个。第一个是很多人看到"负责人"和"门店"都是首行才有值，就一件事干到底：三列一起FillDown。结果看上去没问题，但一旦中间出现了"门店A、负责人A、门店B、负责人空"这种错位记录，负责人的填充结果就会把A负责人的名字填进B门店的空行里，产生"门店B负责人A"的错误数据。解决的办法就是上面写的，先填门店+负责人，再填城市，让负责人牢牢绑定在门店之内。

第二个坑是排序问题。这份表在原始Excel中本身就是按日期、城市、门店排好序的。但如果你的数据源里没有排好序，或者从数据库里导入后乱序了，必须先对城市和门店排序，再执行填充，否则FillDown会把垃圾顺序的上一行值填下来。排序这一步通常放在填充之前，使用Table.Sort按"城市、门店、日期"多重排序。

6. 常见问题与排查技巧实录

6.1 为什么FillDown后还是有空值

最常见的三个原因：一是空单元格其实是空字符串""而不是null；二是第一行本身就是null，没有可用于填充的"上一行"；三是分组填充时组内第一行还是null，需要改用FillUp从组内找结尾。

排查方法：在PowerQuery编辑器里点某一列的下拉筛选，看筛选出来的null和空字符串是不是都存在。如果看到空白和一个空字符串选项，那就要先做替换。用Table.ReplaceValue把空字符串统一替换成null，再执行填充：

let 源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content], 替换空串 = Table.ReplaceValue(源, "", null, Replacer.ReplaceValue, {"日期", "门店", "负责人"}), 填充 = Table.FillDown(替换空串, {"门店", "负责人"}) in 填充

6.2 合并单元格带来的额外负担

从Excel直接读取带有合并单元格的表时，PowerQuery通常只保留左上角的值。这不算Bug，反而是合理行为。处理方式就是先填充再展开。但有些人会遇到"合并单元格跨越多列"的情况，比如"负责人"合并了两列，导入后其中一列是null，另一列有值。这时候先FillDown，再把两列合并成新列，就能得到正确的负责人。

6.3 性能问题：几万行以上的填充卡顿

Table.FillDown本身的性能不错，但如果你在填充前用了一个很复杂的自定义列，或者把整张表展开成了很多列，填充就会随着列数增加而变慢。我的经验是：优先减少列数，把不需要参与填充的列先移除，填充完成后再追加回来。这样既保证了填充速度，又不丢失信息。

另外，如果数据量超过几十万行，Table.Group的分组填充可能会比全局FillDown慢，因为分组本身有开销。此时可以先按"组边界列"排序，再用全局FillDown加辅助列的方式实现，反而更快。

表格汇总常见问题如下：

6.4 关于"excel加载项被禁用"这类问题

有时候PowerQuery查询做好之后，因为Excel加载项被禁用而导致刷新失败。这个问题的排查路径通常是：打开Excel的"文件→选项→加载项"，检查"COM加载项"中有没有禁用Power Query相关的加载项；或者换一种方式，在"数据→查询和连接"里手动刷新。遇到这类问题不要慌，和数据本身无关，属于Excel环境层面的设置。如果禁用列表里躺着Power Query的东西，重新启用再刷新即可。

6.5 调试技巧：不要盯着整张表看

调试FillDown时最忌讳的是整个预览列表几百列一起看，眼睛根本盯不过来。我的做法是：先复制一份查询，把无关列全部删掉，只留参与填充的几列；然后手动构造一小段只有几十行的测试数据，跑通了再回去改原始查询。这样调试速度快得多，也不会因为数据量大分心。

7. 最后再补充一点实操上的体会

做动态填充这件事，真正考验人的不是函数语法，而是能不能把业务数据里"逻辑上的分组"翻译成PowerQuery里的"列和行关系"。很多人在网上搜到FillDown就直接用，遇到串组就说是PowerQuery不够智能，其实问题往往出在对数据结构的理解上。我的建议是：拿到任何一张需要填充的表，先花两分钟做三个动作——检查排序、检查null和空字符串的区别、确认分组边界列是哪些。这三件事做完了，填充步骤基本就能一次性写好。

再分享一个小技巧：如果你需要在每个分组内部填充，但担心Table.Combine之后原有排序会乱掉，可以在分组前加一个Table.AddIndexColumn作为序号列，分组填充完合并后，按序号列重新排序，再删掉序号列。这个方法看着笨，但能保证最后的输出顺序和原表完全一致，在对接其他报表时非常有用。

PowerQuery里的动态填充只是整个数据清洗流程里的一小步，但它经常是让一张"见不了人"的报表变成"能直接交给业务方"的关键一步。希望这篇记录能帮你少踩几个坑，把填充这件事从"手动拉下拉"真正升级成"自动化流程里的一环"。

我实际做下来的体会是：能用原生Table.FillDown解决的，绝不写自定义函数；但一旦遇到跨组、跨条件、跨方向的复杂填充，也不要害怕写List.Generate——只要性能在可接受范围内，写清楚逻辑比什么都重要。动态填充不是魔法，它只是在告诉你：数据表里每一行的"上下文"本身，就是最可靠的参考信息。