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或者从某些数据库导入空白可能会变成空字符串。空字符串不是nullTable.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先看最简单的场景。假设从系统导出的数据长这样日期部门负责人2024-01-05销售部张三nullnullnull2024-01-07null李四nullnullnull你在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组合来得快。这里我的建议很简单动态填充问题优先考虑组合拳。FillDownFillUpTable.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加辅助列的方式实现反而更快。表格汇总常见问题如下现象可能原因解决思路填充后仍有空值空字符串未转null先用Table.ReplaceValue替换空串填充结果串组分组边界列未参与分组改用Table.Group在每个组内填充首次填充后列类型错乱列含null导致类型推断失败手动设置列类型后再填充数据量大时卡顿列数过多或分组开销大精简列、调整填充顺序下拉填充和FillDown结果不一致下拉会延续格式FillDown只认值以业务字段为准不依赖Excel格式填出来的值不对排序混乱先Table.Sort再填充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——只要性能在可接受范围内写清楚逻辑比什么都重要。动态填充不是魔法它只是在告诉你数据表里每一行的上下文本身就是最可靠的参考信息。