做数据分析的人应该都有过这种经历从业务系统导出来的表看着整整齐齐一用就发现根本不是那么回事。尤其是那种一个单元格里塞了五六个值的数据比如订单表里一个订单对应多个商品所有商品ID挤在一个格子里用逗号隔开又或者一张宽表日期、区域、品类全铺在列上想分析的时候却需要让它们站成一排。这类问题在PowerBI里其实有非常成熟的解法核心就是PowerQuery里的两个动作按列分行和按列分列。也就是说一个是把一列里的多个值拆到多行一个是把一列里的多个值拆到多列。再加上一个经常被混在一起说的逆透视列基本能覆盖日常90%以上的表格重排需求。这篇文章我会把这两个操作从原理到实操完整拆一遍用一个贯穿始终的订单数据案例演示每一步怎么做还会把我在实际项目中踩过的坑和排查思路整理出来。适合刚接触PowerQuery的初学者照着抄也适合已经会用一些功能但老是弄混拆分逻辑的进阶用户系统梳理一遍。1. 先搞清楚要分的是行还是列两种场景的本质区别1.1 按列分列到底解的是什么问题按列分列在PowerQuery的菜单里其实叫拆分列。我习惯把按列分列理解为选中的这一列里每一个单元格都含有多个被分隔符隔开的信息片段我们要把这些片段重新排列到同一行的不同列中。举个例子。你的CRM系统导出过这样的数据客户ID联系方式C00113800000001;zhangsanemail.comC00213900000002;lisiemail.com这里的联系方式里存了手机号和邮箱两个片段用分号隔开。如果不上PowerQuery直接在Excel里用分列也能做但问题在于一旦数据源更新你每次都要重新操作一遍。而在PowerQuery里做拆分列相当于把按照分号拆成两列这个逻辑固化进了查询里刷新数据的时候自动执行。这才是它作为PowerBI清洗数据环节的真正价值。还有一种是智能拆分的场景。比如地址字段是广东省深圳市南山区xx路xx号你要拆出省、市、区用固定分隔符不好使因为你没法确定分隔符在哪个位置。这时候可以用按字符数拆分或从非数字到数字的转换等高级选项。这些都属于按列分列的范畴只是拆分依据从固定的逗号分号变成了位置特征或内容特征。1.2 按列分行和逆透视长表 vs 宽表按列分行和按列分列对应的是同一个功能入口的不同选项。还是拿刚才那个联系方式字段举例如果我们的分析目标是每个客户和号码、邮箱分别建立一行关联记录那就需要把一列拆分成多行而不是多列。客户ID联系方式C00113800000001;zhangsanemail.com分列之后变成客户ID拆分后的值C00113800000001C001zhangsanemail.com这个才是真正的按列分行。但这里必须多说一句在实际项目里还有一个经常被叫作按列分行的操作——逆透视列。两者的区别在于拆分列处理的是一个单元格里有多个值逆透视处理的是多个列名其实是一个字段的不同取值。比如业务给你一张这样的表门店一季度二季度三季度上海店10012090这里的一季度、二季度、三季度其实是季度这个字段的取值你要做趋势分析就必须把这三列变成三行。这个动作在PowerQuery里叫逆透视列本质上是把列变成行。很多人习惯把这个也叫按列分行因为它确实让数据从宽变长了。从功能目标上说拆分行和逆透视列都是把横向铺开的数据变纵向堆叠的数据但底层逻辑完全不同实操时选错入口是新手最常见的错误。2. 实操前先把底子打好数据源与工具入口2.1 进入PowerQuery的三种方式PowerBI里的PowerQuery编辑器入口其实不止一个。我在项目里最常用的是主页 - 转换数据这样会直接进入编辑器的独立窗口。如果你是从Excel里拿数据Excel 2016以上版本也有数据 - 从表格/区域进入PowerQuery和PowerBI里的编辑界面几乎一模一样所以这篇文章讲的操作你在两边都能用。还有一个很多人不知道的入口在PowerBI里选中某个表右键选择编辑查询也能进入PowerQuery。区别不大只是入口路径不同。但有个使用习惯我强烈建议你养成进入PowerQuery编辑器之后先看一眼右侧的查询设置面板里面记录了每一步操作历史。PowerQuery最重要的特性之一就是每一步操作都会生成一个步骤记录你可以在后续随时修改中间的某个步骤而不是像Excel操作那样做一步忘一步。这个特性意味着一次数据整理流程可以反复调整不必从头再来。2.2 判断该用拆分列还是逆透视的四步自查法工具入口清楚了之后下一步是判断数据该用哪种处理逻辑。我在带新人的时候总结过一个四步自查法你可以直接拿去用第一步看目标结构。目标是列数变多行数不变还是行数变多列数变少前者是分列后者大概率是分行或逆透视。第二步看多个值藏在哪。值都缩在同一个单元格里用分隔符隔着——这是拆分列或拆分行。值分散在多个列名里列名本身是数据的一部分——这是逆透视。第三步看分隔符是否统一。同一个字段里如果既有中文逗号又有英文逗号还混着分号拆分之前必须先清洗分隔符否则拆出来的结果会有大量空行和残缺值。第四步看是否要保留原列。拆分的时候PowerQuery默认保留原列你也可以选择不保留。逆透视的时候则要考虑哪些列要作为属性、哪些列要作为值还有没有需要保持原样的上下文列。这个自查过程看起来简单但能帮你省下大量反工时间。我见过太多人上来就点逆透视列结果发现数据里根本不存在需要逆透视的结构纯粹是因为听说这个功能很常用也见过有人对一个包含多个值的单元格反复用替换值手工清理却不知道直接拆分行就能解决。3. 核心实操按分隔符分列与分行的完整步骤3.1 打开拆分对话框看懂三个关键选项当你选中目标列后点击拆分列下拉菜单里会有按分隔符按字符数按大写和小写字符之间的转换按非数字到数字的转换等选项。日常用得最多的是按分隔符。点击后弹出对话框你有三个关键选项需要理解第一个是选择或输入分隔符。下拉框里预置了逗号、分号、制表符、空格、自定义等。这里有个细节PowerQuery里的逗号默认匹配的是英文逗号如果你数据里用的是中文全角逗号拉到底部选自定义然后手动输入中文逗号又或者直接用插入特殊字符来指定。第二个是拆分为——这是分列和分行的分水岭。选择列就是按列分列选择行就是按列分行。这个位置藏得很深很多新手在这里点错导致后续整个数据形态完全不对。第三个是高级选项里的拆分为多个列。比如一列地址拆分成省市区县分隔符相同如果每个单元格里的省市区段数一致可以用按最多列数拆分来保证统一结构。如果各单元格的段数不一致建议用按分隔符拆分到尽可能多的列让系统自动按最多段数的行决定列数。3.2 按列分列实操订单渠道拆列场景演示现在用一个实际案例走一遍完整流程。假设你拿到一张订单表里面有一列渠道订单ID渠道金额A001线上-自营199A002线下-分销350任务要求把渠道列拆成两列一列是销售场景一列是销售方式。操作步骤选中渠道列点击拆分列 - 按分隔符分隔符选择自定义输入-拆分为选择列高级选项里按尽可能多的列拆分点击确定。PowerQuery会生成两个新列默认命名是渠道.1和渠道.2系统还自动执行了一个更改的类型步骤。我一般会马上把新列重命名成销售场景和销售方式然后删掉原始渠道列。最后点击关闭并应用回到PowerBI报表界面。这里要注意PowerQuery默认使用-做分隔符时如果有单元格里出现了多个-比如线上-自营-会员日拆出来的列会超过两列。所以如果你的业务里分隔符本身也可能出现在数据内容里拆分前最好先确认数据里分隔符出现的次数规律。这一步可以用分组依据做一次统计看看分隔符数量分布再决定用固定列数还是尽可能多的列。3.3 按列分行实操商品明细拆行场景演示继续用同一张订单表把场景改一下。现在表里有商品清单列一个订单里包含了多个商品ID和商品数量。订单ID商品清单金额A001P001 x1, P002 x2299A002P003 x1150这个数据里商品清单是由逗号连接的多个商品片段每个片段里又有商品ID和数量两层信息。如果我们要做每个订单对应每个商品的明细分析就需要先按逗号拆分成行得到每个商品的独立一行然后再继续处理。操作步骤选中商品清单列点击拆分列 - 按分隔符分隔符选择逗号拆分为选择行点击确定。此时系统会变成这样订单ID商品清单金额A001P001 x1299A001P002 x2299A002P003 x1150原始订单的金额被自动复制到了每一行这就是拆分行和分列最大的行为差异分列不增加行数分行会让一行变成多行其他列的值自动重复填充。到这一步还没结束。因为P001 x1这个片段里还包含着商品ID和数量两层信息需要再做一次拆分列用空格作为分隔符再拆成两列。整个过程就是分列和分行交替使用非常典型的处理链路。3.4 逆透视列实操季度汇总宽表转长表再看宽表转长表的场景。门店一季度二季度三季度四季度上海店10012090110北京店13095105120要在PowerBI里画一条按季度的趋势折线图这个表是不行的。季度应该在横轴上所以必须把四个季度的列变成一行一行的数据。操作步骤在PowerQuery里选中门店列点击转换 - 逆透视其他列。这里我建议你用逆透视其他列而不是逆透视列因为前者不需要手动勾选所有季度列你只需要选中那些需要保留原样的上下文列这里就是门店其他列自动变成属性值对。点击之后PowerQuery会生成两列默认叫属性和值。属性列里存的是原来那些列名一季度、二季度等值列里存的是数值。然后还有一些细节要处理。把属性列里的季度两个字去掉让它变成纯数字方便后续按季度排序。方法是用替换值把季度替换成空。再把值列的数据类型改成整数。改完之后这张表就可以直接用来生成趋势折线图或者拖进矩阵可视化做交叉分析。逆透视列为什么会这么重要因为PowerBI的很多可视化控件要求数据是长表结构——一个维度列用来做轴一个度量列用来做值。业务系统导出的数据却常常是宽表列名就是维度值。逆透视就是打通这两者之间的桥。4. 进阶场景多级拆分、智能拆分与自定义列4.1 一次拆分不干净时的多级处理链路实际业务中遇到的数据往往比刚才演示的例子复杂得多。常见的一种情况是一个单元格里既有多个字段又有多个记录。比如员工ID名下项目E001项目A/开发;项目B/测试;项目C/运维这里项目A/开发是一个项目记录里面包含项目名和担任角色两个字段记录之间用分号隔开。处理链路是先用分号按拆分行把三个项目变成三行再用/按拆分列把项目名和角色拆开。这样的多级处理在PowerQuery里没有任何问题因为每一步操作都会被记录下来你随时可以插入中间步骤。但这里有一个必须留意的点多级拆分后一定要重新审查每一列的数据类型。尤其当你拆出来的片段里包含数字编号比如P001, PowerQuery有时会自作主张转换成数字1导致原始编号丢失。预防方式是在拆分之后马上检查更改的类型步骤或者干脆在拆分之前就把整列的数据类型设为文本避免自动类型转换。4.2 中文数字混合内容的按字符数拆分还有一种不依赖分隔符的场景。信息片段之间没有逗号但有固定长度。比如月报表202401销售数据你要把中间的202401年份月份字段单独取出来。这种时候按字符数拆分就有用了。选中列后拆分列 - 按字符数输入起始位置和长度就能把固定位置的值切出来。实际项目里另一种常见场景是身份证号、银行卡号等固定长度编码的拆分。但我不建议用固定字符数去拆这种字段因为一旦数据源格式有微调整个拆分就废了。更稳妥的办法是用从示例中添加列或者写M公式提取指定模式。PowerQuery里有个功能叫从示例中添加列你只需要给一两个例子告诉它我要取第7到14位或者我要取前6位它会自动帮你推断规则。这个功能在添加列选项卡下用起来非常顺手尤其适合处理不规则文本。4.3 自定义分隔符陷阱多个分隔符并存讲一个真实踩坑案例。有次我处理一个渠道平台的导出数据里面的用户标签字段是这么存的用户ID用户标签U001高价值新品偏好, 活跃用户U002低价值沉默用户同一个字段里既有中文逗号又有英文逗号还有分号。我直接选择分号做分隔符结果那些用中文逗号连接的值根本没被拆分数据行数少了一半。解决方案是在拆分前做一步清洗分隔符。用替换值功能先把所有中文逗号替换成英文逗号再把所有分号也替换成英文逗号。这样整个字段的分隔符就统一了之后无论拆行还是拆列一步到位。这个坑非常典型几乎隔一段时间就会遇到一次。所以我的建议是凡是遇到手动录入的数据Excel表格、CRM系统、后台管理系统导出的数据第一步不是急着拆分而是先检查这个字段里到底存在多少种不同的分隔符。可以用筛选器查看这个列里唯一值的分布也可以用分组依据统计包含特定字符的行数几分钟就能摸清规律。5. 常见问题与排查技巧实录5.1 分列后列数不一致、错位怎么办这是按列分列最常遇到的问题。同一个字段A行有3个片段B行有5个片段用尽可能多的列拆分后B行多出来的列有值A行对应的列就是null。如果后续用这些列做计算null会造成数据缺失视觉上报表里也会出现大片空白。我的处理经验是拆分之前先用分隔符计数。假设分隔符是逗号你可以添加一个自定义列用Text.Length函数计算每行逗号的数量再按分组依据看最大值和分布。如果发现数量差异很大就要考虑是不是数据录入不规范或者分隔符本身在内容里出现过。如果确认数据本身没问题只是列数不一致可以在拆分后用填充功能或者用合并列重新组合某些字段保证结构统一。5.2 空值和空格带来的坑按列分列时PowerQuery遇到连续两个分隔符比如项目A,,项目B默认会产出一个空值行。在拆分行的时候这会直接产生一行全部为空的数据污染后续计算。最稳妥的清理办法是拆分之后立即加一步筛选行筛选条件设为拆出的列不等于null且不等于空字符串。如果你不需要保留空记录这一步不要省略。还有一个小细节从Excel导入的空白单元格在PowerQuery里会显示为null而从CSV导入的可能显示为空字符串这两种都要清理方法不同。null可以用替换值替换成统一标记空字符串则要替换值把替换掉。实战中建议把这两步合并处理防止后面做数据建模时因为null报错。5.3 数据类型自动转换导致的编号丢失PowerQuery默认会在很多操作之后自动执行一次更改的类型。比如你把P001,P002拆成行后系统默认把结果列设为文本但如果拆出来的纯粹是数字可能会被转成整数类型。这看起来人畜无害其实坑在后续。举个例子你把001拆出来了类型被改成整数变成102变成2。等你回头发现再想改回文本已经丢了前导零原始编号彻底找不回来了。所以处理编码型数据我建议你在拆分操作后马上检查查询设置里的步骤看到更改的类型这个步骤如果里面出现了可疑的整数转换直接删掉这个步骤或者手动把列类型改回文本。当然更治本的方法是在数据源导入阶段就声明好哪些列是文本列用选择列或更改类型把整列设为文本这样后续所有操作都会保留前导零。5.4 大数据量拆分性能变慢怎么办PowerQuery处理几千行数据通常很快但当你处理几十万行、且每行都要拆分成几十行时查询会明显变慢。很多人以为是电脑配置不行其实很多时候是操作步骤顺序的问题。我见过一个典型案例一张20万行的订单表用户先用展开功能处理嵌套表产生了几百万行数据才发现需要拆分于是又加了一步拆分行。PowerQuery每步操作都会重放全部上游数据所以惩罚不是线性增长的。这种情况下我建议尽量在数据导入早期就完成拆分不要等到后面做了大量合并、筛选再拆。另一个办法是拆分前先用删除其他列把无关列去掉减小数据宽度PowerQuery处理行的速度会明显提升。如果实在慢还有一个备用方案把拆分逻辑放到SQL端。如果数据源是数据库直接写个字符串拆分查询或者用数据库自带的Split函数效率比PowerQuery高一大截。PowerQuery在ETL流程里定位是清洗和转换不适合做超大数据的逐行字符串操作。6. 我自己一直在用的三个实操习惯分享几个每次处理分列分行任务时都会主动做的事。第一每做一步拆分就在查询设置里给步骤重命名比如把已拆分列-按分隔符改成拆分商品清单或者按逗号拆用户标签。别小看这个动作PowerQuery查询多了以后步骤名混乱是排查问题最大的绊脚石。第二拆分前一定备份原始列或者至少不要急着删除原列。有时候拆完发现分隔符判断错了原列还在的话只需删掉拆分步骤重新来原列删了就得重新加载数据源。第三所有拆分操作做完之后用数据视图或者按列统计信息检查一下每一列的最大值、最小值、空值数量这个习惯能帮你发现很多隐藏在拆分行里的异常数据。按列分行和按列分列之所以值得单独写一篇是因为它们是PowerQuery数据整理里最常用、也最容易被误用的核心操作。理解两者的本质区别熟悉完整操作链路再把本文提到的坑都提前规避掉处理绝大多数表格重新结构的需求就不会再手忙脚乱了。这套能力练熟之后你会发现PowerBI最花时间的往往不是做可视化而是把数据整理到能用的状态而PowerQuery正是解决这个问题的关键工具。