做數(shù)據分析的人應該都有過這種經歷從業(yè)務系統(tǒng)導出來的表看著整整齊齊一用就發(fā)現(xiàn)根本不是那么回事。尤其是那種一個單元格里塞了五六個值的數(shù)據比如訂單表里一個訂單對應多個商品所有商品ID擠在一個格子里用逗號隔開又或者一張寬表日期、區(qū)域、品類全鋪在列上想分析的時候卻需要讓它們站成一排。這類問題在PowerBI里其實有非常成熟的解法核心就是PowerQuery里的兩個動作按列分行和按列分列。也就是說一個是把一列里的多個值拆到多行一個是把一列里的多個值拆到多列。再加上一個經常被混在一起說的逆透視列基本能覆蓋日常90%以上的表格重排需求。這篇文章我會把這兩個操作從原理到實操完整拆一遍用一個貫穿始終的訂單數(shù)據案例演示每一步怎么做還會把我在實際項目中踩過的坑和排查思路整理出來。適合剛接觸PowerQuery的初學者照著抄也適合已經會用一些功能但老是弄混拆分邏輯的進階用戶系統(tǒng)梳理一遍。1. 先搞清楚要分的是行還是列兩種場景的本質區(qū)別1.1 按列分列到底解的是什么問題按列分列在PowerQuery的菜單里其實叫拆分列。我習慣把按列分列理解為選中的這一列里每一個單元格都含有多個被分隔符隔開的信息片段我們要把這些片段重新排列到同一行的不同列中。舉個例子。你的CRM系統(tǒng)導出過這樣的數(shù)據客戶ID聯(lián)系方式C00113800000001;zhangsanemail.comC00213900000002;lisiemail.com這里的聯(lián)系方式里存了手機號和郵箱兩個片段用分號隔開。如果不上PowerQuery直接在Excel里用分列也能做但問題在于一旦數(shù)據源更新你每次都要重新操作一遍。而在PowerQuery里做拆分列相當于把按照分號拆成兩列這個邏輯固化進了查詢里刷新數(shù)據的時候自動執(zhí)行。這才是它作為PowerBI清洗數(shù)據環(huán)節(jié)的真正價值。還有一種是智能拆分的場景。比如地址字段是廣東省深圳市南山區(qū)xx路xx號你要拆出省、市、區(qū)用固定分隔符不好使因為你沒法確定分隔符在哪個位置。這時候可以用按字符數(shù)拆分或從非數(shù)字到數(shù)字的轉換等高級選項。這些都屬于按列分列的范疇只是拆分依據從固定的逗號分號變成了位置特征或內容特征。1.2 按列分行和逆透視長表 vs 寬表按列分行和按列分列對應的是同一個功能入口的不同選項。還是拿剛才那個聯(lián)系方式字段舉例如果我們的分析目標是每個客戶和號碼、郵箱分別建立一行關聯(lián)記錄那就需要把一列拆分成多行而不是多列?？蛻鬒D聯(lián)系方式C00113800000001;zhangsanemail.com分列之后變成客戶ID拆分后的值C00113800000001C001zhangsanemail.com這個才是真正的按列分行。但這里必須多說一句在實際項目里還有一個經常被叫作按列分行的操作——逆透視列。兩者的區(qū)別在于拆分列處理的是一個單元格里有多個值逆透視處理的是多個列名其實是一個字段的不同取值。比如業(yè)務給你一張這樣的表門店一季度二季度三季度上海店10012090這里的一季度、二季度、三季度其實是季度這個字段的取值你要做趨勢分析就必須把這三列變成三行。這個動作在PowerQuery里叫逆透視列本質上是把列變成行。很多人習慣把這個也叫按列分行因為它確實讓數(shù)據從寬變長了。從功能目標上說拆分行和逆透視列都是把橫向鋪開的數(shù)據變縱向堆疊的數(shù)據但底層邏輯完全不同實操時選錯入口是新手最常見的錯誤。2. 實操前先把底子打好數(shù)據源與工具入口2.1 進入PowerQuery的三種方式PowerBI里的PowerQuery編輯器入口其實不止一個。我在項目里最常用的是主頁 - 轉換數(shù)據這樣會直接進入編輯器的獨立窗口。如果你是從Excel里拿數(shù)據Excel 2016以上版本也有數(shù)據 - 從表格/區(qū)域進入PowerQuery和PowerBI里的編輯界面幾乎一模一樣所以這篇文章講的操作你在兩邊都能用。還有一個很多人不知道的入口在PowerBI里選中某個表右鍵選擇編輯查詢也能進入PowerQuery。區(qū)別不大只是入口路徑不同。但有個使用習慣我強烈建議你養(yǎng)成進入PowerQuery編輯器之后先看一眼右側的查詢設置面板里面記錄了每一步操作歷史。PowerQuery最重要的特性之一就是每一步操作都會生成一個步驟記錄你可以在后續(xù)隨時修改中間的某個步驟而不是像Excel操作那樣做一步忘一步。這個特性意味著一次數(shù)據整理流程可以反復調整不必從頭再來。2.2 判斷該用拆分列還是逆透視的四步自查法工具入口清楚了之后下一步是判斷數(shù)據該用哪種處理邏輯。我在帶新人的時候總結過一個四步自查法你可以直接拿去用第一步看目標結構。目標是列數(shù)變多行數(shù)不變還是行數(shù)變多列數(shù)變少前者是分列后者大概率是分行或逆透視。第二步看多個值藏在哪。值都縮在同一個單元格里用分隔符隔著——這是拆分列或拆分行。值分散在多個列名里列名本身是數(shù)據的一部分——這是逆透視。第三步看分隔符是否統(tǒng)一。同一個字段里如果既有中文逗號又有英文逗號還混著分號拆分之前必須先清洗分隔符否則拆出來的結果會有大量空行和殘缺值。第四步看是否要保留原列。拆分的時候PowerQuery默認保留原列你也可以選擇不保留。逆透視的時候則要考慮哪些列要作為屬性、哪些列要作為值還有沒有需要保持原樣的上下文列。這個自查過程看起來簡單但能幫你省下大量反工時間。我見過太多人上來就點逆透視列結果發(fā)現(xiàn)數(shù)據里根本不存在需要逆透視的結構純粹是因為聽說這個功能很常用也見過有人對一個包含多個值的單元格反復用替換值手工清理卻不知道直接拆分行就能解決。3. 核心實操按分隔符分列與分行的完整步驟3.1 打開拆分對話框看懂三個關鍵選項當你選中目標列后點擊拆分列下拉菜單里會有按分隔符按字符數(shù)按大寫和小寫字符之間的轉換按非數(shù)字到數(shù)字的轉換等選項。日常用得最多的是按分隔符。點擊后彈出對話框你有三個關鍵選項需要理解第一個是選擇或輸入分隔符。下拉框里預置了逗號、分號、制表符、空格、自定義等。這里有個細節(jié)PowerQuery里的逗號默認匹配的是英文逗號如果你數(shù)據里用的是中文全角逗號拉到底部選自定義然后手動輸入中文逗號又或者直接用插入特殊字符來指定。第二個是拆分為——這是分列和分行的分水嶺。選擇列就是按列分列選擇行就是按列分行。這個位置藏得很深很多新手在這里點錯導致后續(xù)整個數(shù)據形態(tài)完全不對。第三個是高級選項里的拆分為多個列。比如一列地址拆分成省市區(qū)縣分隔符相同如果每個單元格里的省市區(qū)段數(shù)一致可以用按最多列數(shù)拆分來保證統(tǒng)一結構。如果各單元格的段數(shù)不一致建議用按分隔符拆分到盡可能多的列讓系統(tǒng)自動按最多段數(shù)的行決定列數(shù)。3.2 按列分列實操訂單渠道拆列場景演示現(xiàn)在用一個實際案例走一遍完整流程。假設你拿到一張訂單表里面有一列渠道訂單ID渠道金額A001線上-自營199A002線下-分銷350任務要求把渠道列拆成兩列一列是銷售場景一列是銷售方式。操作步驟選中渠道列點擊拆分列 - 按分隔符分隔符選擇自定義輸入-拆分為選擇列高級選項里按盡可能多的列拆分點擊確定。PowerQuery會生成兩個新列默認命名是渠道.1和渠道.2系統(tǒng)還自動執(zhí)行了一個更改的類型步驟。我一般會馬上把新列重命名成銷售場景和銷售方式然后刪掉原始渠道列。最后點擊關閉并應用回到PowerBI報表界面。這里要注意PowerQuery默認使用-做分隔符時如果有單元格里出現(xiàn)了多個-比如線上-自營-會員日拆出來的列會超過兩列。所以如果你的業(yè)務里分隔符本身也可能出現(xiàn)在數(shù)據內容里拆分前最好先確認數(shù)據里分隔符出現(xiàn)的次數(shù)規(guī)律。這一步可以用分組依據做一次統(tǒng)計看看分隔符數(shù)量分布再決定用固定列數(shù)還是盡可能多的列。3.3 按列分行實操商品明細拆行場景演示繼續(xù)用同一張訂單表把場景改一下?，F(xiàn)在表里有商品清單列一個訂單里包含了多個商品ID和商品數(shù)量。訂單ID商品清單金額A001P001 x1, P002 x2299A002P003 x1150這個數(shù)據里商品清單是由逗號連接的多個商品片段每個片段里又有商品ID和數(shù)量兩層信息。如果我們要做每個訂單對應每個商品的明細分析就需要先按逗號拆分成行得到每個商品的獨立一行然后再繼續(xù)處理。操作步驟選中商品清單列點擊拆分列 - 按分隔符分隔符選擇逗號拆分為選擇行點擊確定。此時系統(tǒng)會變成這樣訂單ID商品清單金額A001P001 x1299A001P002 x2299A002P003 x1150原始訂單的金額被自動復制到了每一行這就是拆分行和分列最大的行為差異分列不增加行數(shù)分行會讓一行變成多行其他列的值自動重復填充。到這一步還沒結束。因為P001 x1這個片段里還包含著商品ID和數(shù)量兩層信息需要再做一次拆分列用空格作為分隔符再拆成兩列。整個過程就是分列和分行交替使用非常典型的處理鏈路。3.4 逆透視列實操季度匯總寬表轉長表再看寬表轉長表的場景。門店一季度二季度三季度四季度上海店10012090110北京店13095105120要在PowerBI里畫一條按季度的趨勢折線圖這個表是不行的。季度應該在橫軸上所以必須把四個季度的列變成一行一行的數(shù)據。操作步驟在PowerQuery里選中門店列點擊轉換 - 逆透視其他列。這里我建議你用逆透視其他列而不是逆透視列因為前者不需要手動勾選所有季度列你只需要選中那些需要保留原樣的上下文列這里就是門店其他列自動變成屬性值對。點擊之后PowerQuery會生成兩列默認叫屬性和值。屬性列里存的是原來那些列名一季度、二季度等值列里存的是數(shù)值。然后還有一些細節(jié)要處理。把屬性列里的季度兩個字去掉讓它變成純數(shù)字方便后續(xù)按季度排序。方法是用替換值把季度替換成空。再把值列的數(shù)據類型改成整數(shù)。改完之后這張表就可以直接用來生成趨勢折線圖或者拖進矩陣可視化做交叉分析。逆透視列為什么會這么重要因為PowerBI的很多可視化控件要求數(shù)據是長表結構——一個維度列用來做軸一個度量列用來做值。業(yè)務系統(tǒng)導出的數(shù)據卻常常是寬表列名就是維度值。逆透視就是打通這兩者之間的橋。4. 進階場景多級拆分、智能拆分與自定義列4.1 一次拆分不干凈時的多級處理鏈路實際業(yè)務中遇到的數(shù)據往往比剛才演示的例子復雜得多。常見的一種情況是一個單元格里既有多個字段又有多個記錄。比如員工ID名下項目E001項目A/開發(fā);項目B/測試;項目C/運維這里項目A/開發(fā)是一個項目記錄里面包含項目名和擔任角色兩個字段記錄之間用分號隔開。處理鏈路是先用分號按拆分行把三個項目變成三行再用/按拆分列把項目名和角色拆開。這樣的多級處理在PowerQuery里沒有任何問題因為每一步操作都會被記錄下來你隨時可以插入中間步驟。但這里有一個必須留意的點多級拆分后一定要重新審查每一列的數(shù)據類型。尤其當你拆出來的片段里包含數(shù)字編號比如P001, PowerQuery有時會自作主張轉換成數(shù)字1導致原始編號丟失。預防方式是在拆分之后馬上檢查更改的類型步驟或者干脆在拆分之前就把整列的數(shù)據類型設為文本避免自動類型轉換。4.2 中文數(shù)字混合內容的按字符數(shù)拆分還有一種不依賴分隔符的場景。信息片段之間沒有逗號但有固定長度。比如月報表202401銷售數(shù)據你要把中間的202401年份月份字段單獨取出來。這種時候按字符數(shù)拆分就有用了。選中列后拆分列 - 按字符數(shù)輸入起始位置和長度就能把固定位置的值切出來。實際項目里另一種常見場景是身份證號、銀行卡號等固定長度編碼的拆分。但我不建議用固定字符數(shù)去拆這種字段因為一旦數(shù)據源格式有微調整個拆分就廢了。更穩(wěn)妥的辦法是用從示例中添加列或者寫M公式提取指定模式。PowerQuery里有個功能叫從示例中添加列你只需要給一兩個例子告訴它我要取第7到14位或者我要取前6位它會自動幫你推斷規(guī)則。這個功能在添加列選項卡下用起來非常順手尤其適合處理不規(guī)則文本。4.3 自定義分隔符陷阱多個分隔符并存講一個真實踩坑案例。有次我處理一個渠道平臺的導出數(shù)據里面的用戶標簽字段是這么存的用戶ID用戶標簽U001高價值新品偏好, 活躍用戶U002低價值沉默用戶同一個字段里既有中文逗號又有英文逗號還有分號。我直接選擇分號做分隔符結果那些用中文逗號連接的值根本沒被拆分數(shù)據行數(shù)少了一半。解決方案是在拆分前做一步清洗分隔符。用替換值功能先把所有中文逗號替換成英文逗號再把所有分號也替換成英文逗號。這樣整個字段的分隔符就統(tǒng)一了之后無論拆行還是拆列一步到位。這個坑非常典型幾乎隔一段時間就會遇到一次。所以我的建議是凡是遇到手動錄入的數(shù)據Excel表格、CRM系統(tǒng)、后臺管理系統(tǒng)導出的數(shù)據第一步不是急著拆分而是先檢查這個字段里到底存在多少種不同的分隔符?？梢杂煤Y選器查看這個列里唯一值的分布也可以用分組依據統(tǒng)計包含特定字符的行數(shù)幾分鐘就能摸清規(guī)律。5. 常見問題與排查技巧實錄5.1 分列后列數(shù)不一致、錯位怎么辦這是按列分列最常遇到的問題。同一個字段A行有3個片段B行有5個片段用盡可能多的列拆分后B行多出來的列有值A行對應的列就是null。如果后續(xù)用這些列做計算null會造成數(shù)據缺失視覺上報表里也會出現(xiàn)大片空白。我的處理經驗是拆分之前先用分隔符計數(shù)。假設分隔符是逗號你可以添加一個自定義列用Text.Length函數(shù)計算每行逗號的數(shù)量再按分組依據看最大值和分布。如果發(fā)現(xiàn)數(shù)量差異很大就要考慮是不是數(shù)據錄入不規(guī)范或者分隔符本身在內容里出現(xiàn)過。如果確認數(shù)據本身沒問題只是列數(shù)不一致可以在拆分后用填充功能或者用合并列重新組合某些字段保證結構統(tǒng)一。5.2 空值和空格帶來的坑按列分列時PowerQuery遇到連續(xù)兩個分隔符比如項目A,,項目B默認會產出一個空值行。在拆分行的時候這會直接產生一行全部為空的數(shù)據污染后續(xù)計算。最穩(wěn)妥的清理辦法是拆分之后立即加一步篩選行篩選條件設為拆出的列不等于null且不等于空字符串。如果你不需要保留空記錄這一步不要省略。還有一個小細節(jié)從Excel導入的空白單元格在PowerQuery里會顯示為null而從CSV導入的可能顯示為空字符串這兩種都要清理方法不同。null可以用替換值替換成統(tǒng)一標記空字符串則要替換值把替換掉。實戰(zhàn)中建議把這兩步合并處理防止后面做數(shù)據建模時因為null報錯。5.3 數(shù)據類型自動轉換導致的編號丟失PowerQuery默認會在很多操作之后自動執(zhí)行一次更改的類型。比如你把P001,P002拆成行后系統(tǒng)默認把結果列設為文本但如果拆出來的純粹是數(shù)字可能會被轉成整數(shù)類型。這看起來人畜無害其實坑在后續(xù)。舉個例子你把001拆出來了類型被改成整數(shù)變成102變成2。等你回頭發(fā)現(xiàn)再想改回文本已經丟了前導零原始編號徹底找不回來了。所以處理編碼型數(shù)據我建議你在拆分操作后馬上檢查查詢設置里的步驟看到更改的類型這個步驟如果里面出現(xiàn)了可疑的整數(shù)轉換直接刪掉這個步驟或者手動把列類型改回文本。當然更治本的方法是在數(shù)據源導入階段就聲明好哪些列是文本列用選擇列或更改類型把整列設為文本這樣后續(xù)所有操作都會保留前導零。5.4 大數(shù)據量拆分性能變慢怎么辦PowerQuery處理幾千行數(shù)據通常很快但當你處理幾十萬行、且每行都要拆分成幾十行時查詢會明顯變慢。很多人以為是電腦配置不行其實很多時候是操作步驟順序的問題。我見過一個典型案例一張20萬行的訂單表用戶先用展開功能處理嵌套表產生了幾百萬行數(shù)據才發(fā)現(xiàn)需要拆分于是又加了一步拆分行。PowerQuery每步操作都會重放全部上游數(shù)據所以懲罰不是線性增長的。這種情況下我建議盡量在數(shù)據導入早期就完成拆分不要等到后面做了大量合并、篩選再拆。另一個辦法是拆分前先用刪除其他列把無關列去掉減小數(shù)據寬度PowerQuery處理行的速度會明顯提升。如果實在慢還有一個備用方案把拆分邏輯放到SQL端。如果數(shù)據源是數(shù)據庫直接寫個字符串拆分查詢或者用數(shù)據庫自帶的Split函數(shù)效率比PowerQuery高一大截。PowerQuery在ETL流程里定位是清洗和轉換不適合做超大數(shù)據的逐行字符串操作。6. 我自己一直在用的三個實操習慣分享幾個每次處理分列分行任務時都會主動做的事。第一每做一步拆分就在查詢設置里給步驟重命名比如把已拆分列-按分隔符改成拆分商品清單或者按逗號拆用戶標簽。別小看這個動作PowerQuery查詢多了以后步驟名混亂是排查問題最大的絆腳石。第二拆分前一定備份原始列或者至少不要急著刪除原列。有時候拆完發(fā)現(xiàn)分隔符判斷錯了原列還在的話只需刪掉拆分步驟重新來原列刪了就得重新加載數(shù)據源。第三所有拆分操作做完之后用數(shù)據視圖或者按列統(tǒng)計信息檢查一下每一列的最大值、最小值、空值數(shù)量這個習慣能幫你發(fā)現(xiàn)很多隱藏在拆分行里的異常數(shù)據。按列分行和按列分列之所以值得單獨寫一篇是因為它們是PowerQuery數(shù)據整理里最常用、也最容易被誤用的核心操作。理解兩者的本質區(qū)別熟悉完整操作鏈路再把本文提到的坑都提前規(guī)避掉處理絕大多數(shù)表格重新結構的需求就不會再手忙腳亂了。這套能力練熟之后你會發(fā)現(xiàn)PowerBI最花時間的往往不是做可視化而是把數(shù)據整理到能用的狀態(tài)而PowerQuery正是解決這個問題的關鍵工具。