Power Query 真經(jīng) - 第 8 章 - 縱向追加數(shù)據(jù)

數(shù)據(jù)專業(yè)人員經(jīng)常做的工作之一是將多個(gè)數(shù)據(jù)集追加到一起。無論這些數(shù)據(jù)集是包含在一個(gè) Excel 工作簿中，還是分布在多個(gè)文件中，問題是它們需要被縱向【追加】到一個(gè)表中。

類似這一需求的一個(gè)常見場(chǎng)景是，每月從中央數(shù)據(jù)庫中提取的數(shù)據(jù)需要合并用來進(jìn)行年初至今的分析。在 2 月份，用戶提取了 1 月份的數(shù)據(jù)，并將其發(fā)送給分析師。然后在 3 月份的時(shí)候，用戶又將 2 月份的數(shù)據(jù)發(fā)送給分析師，分析師將數(shù)據(jù)添加到解決方案中，如此循環(huán)，按月持續(xù)到全年。

處理這種解決方案的經(jīng)典 Excel 流程最初通常可以歸結(jié)為以下幾點(diǎn)。

1. 將一月份的文件導(dǎo)入并轉(zhuǎn)換為表格格式。

2. 將數(shù)據(jù)轉(zhuǎn)化為正式的 Excel 表格。

3. 根據(jù) Excel 表格建立分析報(bào)告。

4. 保存該文件。

然后，在每月的基礎(chǔ)上按進(jìn)行如下操作。

1. 導(dǎo)入并轉(zhuǎn)換新收到的數(shù)據(jù)文件。

2. 復(fù)制新的數(shù)據(jù)，并將其粘貼到原始表格的末尾。

3. 刷新報(bào)告和視覺對(duì)象。

雖然可以這樣做，但這個(gè)過程顯然不是夠完美的，因?yàn)檫@里有一些非常明顯的問題。本章不會(huì)解決用戶在轉(zhuǎn)換中觸發(fā)錯(cuò)誤的問題（盡管以后的章節(jié)會(huì)解決），但會(huì)向用戶展示 Power Query 如何合并兩個(gè)或更多的數(shù)據(jù)集，而不必?fù)?dān)心用戶把最后幾行的數(shù)據(jù)粘貼過來導(dǎo)致數(shù)據(jù)重復(fù)。

8.1 基本追加

“第 08 章 示例文件” 包含三個(gè) “CSV” 文件：“Jan 2008.csv”、“Feb 2008.csv” 和 “Mar 2008.csv”。本節(jié)將介紹導(dǎo)入和追加每個(gè)文件的過程。

導(dǎo)入文件非常簡(jiǎn)單，如下所示。

1. 創(chuàng)建一個(gè)新的查詢【來自文件】【從文本 / CSV】。

2. 瀏覽 “第 08 章 示例文件 / Jan 2008.csv”【導(dǎo)入】【轉(zhuǎn)換數(shù)據(jù)】。

Power Query 將打開該文件，并為該數(shù)據(jù)源自動(dòng)執(zhí)行以下步驟。

1. 將第一行提升為標(biāo)題，顯示列為：“Date”、“Account” 、“Dept” 和 “Amount”。

2. 數(shù)據(jù)類型自動(dòng)轉(zhuǎn)換為【日期】、【整數(shù)】、【整數(shù)】和【小數(shù)】。

為了數(shù)據(jù)類型的轉(zhuǎn)換更加穩(wěn)妥，不再依賴于系統(tǒng)默認(rèn)的自動(dòng)轉(zhuǎn)換，這里刪除 “Changed Type” 步驟，并重新創(chuàng)建它，迫使 “Date” 根據(jù)它的來源數(shù)據(jù)格式美國標(biāo)準(zhǔn)導(dǎo)入。

1. 刪除 “Changed Type” 步驟。

2. 更改 “Date” 列的數(shù)據(jù)類型【使用區(qū)域設(shè)置】【日期】【英語 (美國)】【確定】。

3. 更改 “Amount” 列的數(shù)據(jù)類型【使用區(qū)域設(shè)置】【貨幣】【英語 (美國)】【確定】。

4. 更改 “Account” 列的數(shù)據(jù)類型【整數(shù)】。

5. 更改 “Dept” 列的數(shù)據(jù)類型【整數(shù)】。

此時(shí)，查詢將如圖 8-1 所示。

圖 8-1 加載前的 “Jan 2008” 查詢

由于用戶的目標(biāo)不是只報(bào)告一月份的銷售情況，所以此時(shí)把這個(gè)查詢只作為一個(gè)連接來加載，為以后追加數(shù)據(jù)做準(zhǔn)備。

【注意】

在 Power BI 中，可以右擊查詢，取消勾選【啟用加載】復(fù)選框，而在 Excel 中，需要轉(zhuǎn)到【主頁】【關(guān)閉并上載至】【僅創(chuàng)建連接】【確定】。

現(xiàn)在用完全相同的步驟導(dǎo)入 “Feb 2008.csv” 和 “Mar 2008.csv” 文件，導(dǎo)入完成后應(yīng)該有如下所示的三個(gè)新查詢，每個(gè)都作為一個(gè)連接加載。

1. Jan 2008。

2. Feb 2008。

3. Mar 2008。

完成后，三個(gè)查詢都應(yīng)該在 Excel 的【查詢 & 連接】窗格中，或在 Power Query 編輯器的【查詢】導(dǎo)航窗格中也可看見，如圖 8-2 所示。

圖 8-2 這些查詢顯示在 Excel 的【查詢 & 連接】窗格（左）和在 Power Query【查詢】導(dǎo)航窗格（右）

8.1.1 追加兩個(gè)表

下一項(xiàng)工作是創(chuàng)建用于后續(xù)分析的整個(gè)表，這需要將上述表格追加在一起。在 Excel 中完成這項(xiàng)工作的一個(gè)方法是，右擊【查詢 & 連接】窗格中的任意一個(gè)查詢，并選擇【追加】。此時(shí)將彈出如圖 8-3 所示的對(duì)話框。

圖 8-3 【追加】查詢對(duì)話框

雖然這看起來相當(dāng)容易，但實(shí)際上建議用戶不要使用這個(gè)功能來追加表。是的，它允許用戶追加兩個(gè)查詢（如果有需要，的確可以將一個(gè)查詢追加到自身）。它甚至允許用戶一次性追加多個(gè)表，只需要切換到【三個(gè)或更多表】視圖進(jìn)行操作。但這里有一些注意事項(xiàng)。

1. 在 Power BI 中沒有【查詢 & 連接】窗格，建議用戶學(xué)習(xí)一種能在多個(gè)程序中都適用的方法來做到這一點(diǎn)。

2. 這將創(chuàng)建一個(gè)名為 “Append 1” 的新查詢，它將所有合并的表合并到【應(yīng)用的步驟】窗口中的一個(gè) “Source” 步驟中，使得檢查更加困難。

與其使用這種功能，更建議用戶學(xué)會(huì)對(duì)第一個(gè)表進(jìn)行【引用】，然后在 Power Query 編輯器里面執(zhí)行【追加】操作。這些方法的主要區(qū)別在于，這個(gè)方法可以在任何擁有 Power Query 的工具上工作，而且它還會(huì)為【追加】到查詢的每個(gè)表記錄一個(gè)不同的 “Appended Query（追加的查詢）” 步驟。有了不同的步驟，以后檢查查詢變得非常容易，而不是把未知數(shù)量的查詢都合并到一個(gè) “Source” 步驟中。

將選擇使用 “Jan 2008” 查詢作為最初的數(shù)據(jù)，并向它追加（僅）“Feb 2008” 查詢。

1. 右擊任意一個(gè)查詢【編輯】，然后展開【查詢】導(dǎo)航器窗格。

2. 右擊 “Jan 2008” 查詢【引用】。

3. 將 “Jan 2008” 查詢重命名為 “Transactions”。

4. 轉(zhuǎn)到【主頁】【追加查詢】。

5. 【要追加的表】選擇 “Feb 2008”【確定】。

此時(shí)的結(jié)果將如圖 8-4 所示。

圖 8-4 將 “Feb 2008” 查詢追加到 “Transactions” 查詢的結(jié)果

【注意】

如果用戶直接從 Excel 用戶界面【追加】查詢（或者在 Power Query 窗口中，選擇 “Jan 2008” 查詢后直接轉(zhuǎn)到【主頁】【將查詢追加為新查詢】），會(huì)有一個(gè)名為 “Source” 的步驟。雖然【應(yīng)用的步驟】窗口中的步驟會(huì)比較少，但這意味著用戶必須單擊 “Source” 步驟，并閱讀公式欄來了解發(fā)生了什么。在這個(gè)示例中，檢查跟蹤非常清楚，因?yàn)?“Source” 步驟指向 “Jan 2008” 查詢，可以清楚地看到另一個(gè)查詢被【追加】到了這個(gè)數(shù)據(jù)上。

此時(shí)，用戶可能很想向下滾動(dòng)查詢，看看是否所有的記錄都在那里。不幸的是，這并沒有顯示全部數(shù)據(jù)，因?yàn)?Power Query 實(shí)際上并不會(huì)在窗口加載所有的數(shù)據(jù)，而是顯示數(shù)據(jù)的預(yù)覽。它顯示的行數(shù)隨用戶添加的數(shù)據(jù)而變化，可以在 Power Query 編輯器的左下角看到這一點(diǎn)，如圖 8-5 所示。

圖 8-5 Power Query 向用戶顯示了它現(xiàn)在可以處理的預(yù)覽行數(shù)

當(dāng)然，這里存在一個(gè)問題：如果用戶不能看到所有的數(shù)據(jù)，那怎么知道數(shù)據(jù)是否成功追加了呢？答案是要加載查詢。所以把數(shù)據(jù)加載到一個(gè)工作表中，看看能得到什么，結(jié)果將如圖 8-6 所示。

圖 8-6 【查詢 & 連接】窗格顯示，“Transaction” 查詢有 3,887 行記錄

【注意】

要在 Power BI 中查看數(shù)據(jù)量，進(jìn)入【數(shù)據(jù)】視圖（在左側(cè)），在【字段】列表中選擇要查看的表。行數(shù)將顯示在界面的左下方。

為了驗(yàn)證和可視化加載到 Excel 中的數(shù)據(jù)量，可以在這里用數(shù)據(jù)透視表來匯總數(shù)據(jù)。

1. 選擇 “Transaction” 表中的任何單元格【插入】【數(shù)據(jù)透視表】。

2. 將【數(shù)據(jù)透視表】放在當(dāng)前工作表的 F2 單元格中。

3. 將 “Amount” 拖到數(shù)【值】。

4. 將 “Date” 拖到數(shù)【行】。

5. 右擊 F3 單元格【組合】【月（僅）】【確定】。

一旦完成，會(huì)看到有一個(gè)【數(shù)據(jù)透視表】，顯示 “Jan 2008” 表和 “Feb 2008” 表確實(shí)合并為一個(gè)表了，如圖 8-7 所示。

圖 8-7 “Jan 2008” 和 “Feb 2008” 交易數(shù)據(jù)現(xiàn)在在一個(gè)【數(shù)據(jù)透視表】中

8.1.2 追加額外的表

此時(shí)，用戶想把三月的記錄也追加到 “Transaction” 查詢中。這就是那些在【查詢 & 連接】窗格中使用【追加】功能的 Excel 用戶的苦惱所在。他們的本能是右擊 “Transaction” 查詢，然后將三月份的數(shù)據(jù)【追加】到它上面。這種方法的問題是，它將創(chuàng)建一個(gè)新的查詢，而不是將這一步驟添加到 “Transaction” 查詢中。由于【數(shù)據(jù)透視表】是基于 “Transaction” 表的結(jié)果，所以此時(shí)需要在 “Transaction” 查詢中添加新的【追加】步驟，而不是添加一個(gè)新的查詢步驟。

為了將三月的數(shù)據(jù)添加到現(xiàn)有的 “Transactions” 查詢中，需要編輯 “Transactions” 查詢。此時(shí)，用戶需要做出選擇。是編輯現(xiàn)有的 “Appended Query” 步驟，還是添加一個(gè)新的步驟呢？這個(gè)問題的答案實(shí)際上取決于隨著時(shí)間的推移，用戶將向解決方案添加的數(shù)據(jù)量，以及用戶希望檢查跟蹤此查詢的清晰程度。

比方說，用戶將在一段時(shí)間內(nèi)添加 12 個(gè)追加項(xiàng)，并且不希望有一個(gè)很長(zhǎng)的步驟列表。在這種情況下，按如下操作即可。

1. 單擊 “Appended Query” 步驟旁邊的齒輪，彈出的【追加】對(duì)話框選擇【三個(gè)或更多表】。

2. 選擇需要追加的每個(gè)表，單擊【添加】。

此時(shí)結(jié)果如圖 8-8 所示。

圖 8-8 在一個(gè)步驟中添加多個(gè)追加項(xiàng)

或者，如果想要一次執(zhí)行一個(gè)查詢，并專注于創(chuàng)建一個(gè)易于使用的檢查跟蹤路徑，那么可以在每次向數(shù)據(jù)源添加一個(gè)新的查詢時(shí)采取如下操作。

1. 右擊 “Transaction” 查詢【編輯】。

2. 進(jìn)入【主頁】【追加查詢】。

3. 選擇新增加一個(gè)【追加查詢】。

此時(shí)結(jié)果如圖 8-9 所示。

圖 8-9 一次添加一個(gè)查詢，創(chuàng)建不同的步驟

事實(shí)上，用戶如果想讓檢查線索更加清晰，可以右擊步驟名稱并選擇【屬性】，來修改步驟名稱并提供在懸停時(shí)顯示的注釋。

此時(shí)結(jié)果如圖 8-10 所示。

圖 8-10 設(shè)置步驟名稱與工具提示描述

要自定義步驟名稱并添加工具提示，只需右擊步驟并選擇【屬性】。這將允許用戶修改默認(rèn)的步驟名稱，并添加一個(gè)自定義的描述，在鼠標(biāo)懸停在信息圖標(biāo)上時(shí)顯示出來。

【警告】

除了 “Source” 步驟之外的所有步驟都可以用這種方式重命名。要重新命名 “Source” 步驟，需要學(xué)會(huì)編輯查詢的底層 M 代碼。

【注意】

關(guān)于編輯默認(rèn)查詢名稱有兩種觀點(diǎn)。雖然編輯每個(gè)步驟的名稱以使其更具描述性是很誘人的，但對(duì)于一個(gè)真正的 Power Query 專家來說，挑戰(zhàn)在于他們現(xiàn)在需要花更多的時(shí)間來檢查每個(gè)步驟，來理解公式實(shí)際上是什么。本書建議使用默認(rèn)的步驟名稱并與它們的實(shí)際操作聯(lián)系起來，而可以使用 “描述”（【說明】）功能來記錄關(guān)于操作意圖的注釋。

無論用戶決定用哪種方式將三月的表追加到數(shù)據(jù)集上（通過編輯現(xiàn)有的步驟或創(chuàng)建一個(gè)新的步驟），現(xiàn)在都是時(shí)候加載數(shù)據(jù)并驗(yàn)證三月數(shù)據(jù)的追加是否真的成功。

現(xiàn)在，要重新考慮 Power Queries 在加載到 Excel 表格時(shí)的一個(gè)不幸的問題。當(dāng)用戶查看包含【數(shù)據(jù)透視表】的工作表時(shí)，可以看到 “Transaction” 查詢（也就是 Excel 表），確實(shí)保存了所有的 6,084 行數(shù)據(jù)，之前三個(gè)月數(shù)據(jù)的總和。然而，【數(shù)據(jù)透視表】并沒有改變，如圖 8-11 所示。

圖 8-11 “Transaction” 表已經(jīng)更新，但【數(shù)據(jù)透視表】卻沒有更新

這不是什么大問題，只是一個(gè)小小的不便和提醒。如果用戶把數(shù)據(jù)加載到一個(gè) Excel 表中，然后把它放入到一個(gè) 【數(shù)據(jù)透視表】中，是需要刷新【數(shù)據(jù)透視表】，以便讓更新的數(shù)據(jù)流入【數(shù)據(jù)透視表】。

右擊【數(shù)據(jù)透視表】【刷新】。

此時(shí)，【數(shù)據(jù)透視表】確實(shí)更新了，如圖 8-12 所示。

圖 8-12 一月到三月的記錄現(xiàn)在顯示在一個(gè)【數(shù)據(jù)透視表】中

【注意】

記住，如果查詢被加載到 Excel 或 Power BI 的數(shù)據(jù)模型中，點(diǎn)擊一次【刷新】就可以更新數(shù)據(jù)源和任何透視或可視化對(duì)象。

顯然，每月編輯文件來添加和轉(zhuǎn)換新的數(shù)據(jù)源，然后將其【追加】到 “Transactions” 查詢中，這種方法很快就會(huì)過時(shí)。在第 9 章中，將向用戶展示一種更簡(jiǎn)單的方法。但事實(shí)如這里所示，追加和編輯單獨(dú)的追加項(xiàng)，是一項(xiàng)重要的技能，用戶必須掌握它，才能熟練地使用 Power Query。

8.2 追加列標(biāo)題不同的數(shù)據(jù)

在【追加】查詢時(shí)，只要被合并的查詢的列標(biāo)題是相同的，第二個(gè)查詢就會(huì)按用戶所期望的那樣被【追加】到第一個(gè)查詢上。但是，如果這些列沒有相同的列標(biāo)題呢？

如圖 8-13 所示，“Date” 列的名稱在 “Mar 2008” 的查詢中變成了 “TranDate”，而分析師并沒有注意到。當(dāng) “Jan 2008” 和 “Feb 2008” 的記錄被【追加】時(shí)，一切都很正常。但是當(dāng)分析師把 “Mar 2008”【追加】到記錄的表中時(shí)，事情就變得糟糕。

圖 8-13 Power Query 如何知道 “TranDate” 列值應(yīng)該進(jìn)入 “Date” 列呢

當(dāng)【追加】?jī)蓚€(gè)表時(shí)，Power Query 將從第一個(gè)查詢中加載數(shù)據(jù)。然后掃描第二個(gè)（和后續(xù)）查詢的標(biāo)題行。如果任何標(biāo)題不存在于現(xiàn)有列中，新的列將被添加。然后，它將適當(dāng)?shù)挠涗浱钊朊總€(gè)數(shù)據(jù)集的每一列，用 “null” 值填補(bǔ)所有空白。

按這個(gè)邏輯，這意味著 “TranDate” 列（出現(xiàn)在三月的查詢中）在一月和二月中被填充為 “null” 值，因?yàn)?“Jan 2008” 的查詢沒有 “TranDate” 列。

另一方面，由于源文件中的列名改變了，“Mar 2008” 查詢沒有 “Date” 列，而是擁有 “TranDate” 列。“Date” 列為每個(gè)三月記錄填充了 “null” 值，而 “TranDate” 列則保存了本應(yīng)在 “Date” 列中出現(xiàn)的值。

解決這個(gè)問題的方法如下所示。

1. 編輯 “Mar 2008” 的查詢，將 “TranDate” 列重命名為 “Date”。

2. 編輯 “Transaction” 查詢。

3. 轉(zhuǎn)到【主頁】【刷新預(yù)覽】。

公平地說，預(yù)覽應(yīng)該自己刷新，但上面的單擊步驟強(qiáng)制執(zhí)行了這一點(diǎn)。

【注意】

想自己試試嗎？【編輯】其中一個(gè)月度查詢，并將其中任何一列重命名為不同的名稱。返回到 “Transactions” 查詢，此時(shí)將看到新命名的列。

8.3 在當(dāng)前文件中追加表和區(qū)域

雖然從外部文件中檢索和【追加】數(shù)據(jù)是很常見的，但 Excel 用戶也會(huì)使用這種功能來【追加】同一工作簿中的數(shù)據(jù)表。

當(dāng)【追并】少量的表時(shí)，只需要使用上面描述的方法即可。

1. 為每個(gè)數(shù)據(jù)源創(chuàng)建一個(gè)【暫存】（【僅限連接】）查詢。

2. 【引用】表。

3. 追加其他的數(shù)據(jù)。

但是，如果用戶想構(gòu)建一個(gè)體系，其中 Excel 就像一個(gè)準(zhǔn)數(shù)據(jù)庫一樣，用戶按月創(chuàng)建一個(gè)新表，在工作簿中保存該月的交易，會(huì)發(fā)生什么情況？分析師真的想手動(dòng)調(diào)整查詢來每月【追加】一個(gè)新表嗎？并非如此。能否設(shè)置一個(gè)解決方案，在刷新時(shí)自動(dòng)包含所有新表？

這個(gè)問題的答案是肯定的，它涉及到利用在第 6 章中使用的 Excel.CurrentWorkbook 函數(shù)來讀取動(dòng)態(tài)命名范圍。

來看一些具體的例子，從 “第 08 章 示例文件 \Append Tables.xlsx” 開始。

這個(gè)特定的文件包含三個(gè)表，其業(yè)務(wù)表示某水療中心每月發(fā)行的禮品券。每個(gè)工作表都以月和年命名，并用空格隔開，每個(gè)工作表都包含一個(gè)表格。雖然每個(gè)表格也是以年和月命名，但這些日期部分用 “_” 字符分隔的（ Jan_2008，F(xiàn)eb_2008，等）因?yàn)楸砀衩Q中不允許有空格。

每個(gè)月，記賬員都會(huì)勤奮地創(chuàng)建和命名一個(gè)新的工作表，并設(shè)置和命名該表作為他們?cè)履┕ぷ鞯囊徊糠?。他們似乎忽略了一件事，就是把禮品券的發(fā)放日期或到期日期放在表中，如圖 8-14 所示。

圖 8-14 一月份禮品券信息的示例數(shù)據(jù)

那么，如何才能建立一個(gè)解決方案，使它自動(dòng)包含記賬員添加的所有新表，而不必教記賬員如何編輯 Power Query。

8.3.1 合并表

不幸的是，Excel 中沒有按鈕可以對(duì)當(dāng)前工作簿中的可見對(duì)象創(chuàng)建查詢，所以需要去從頭開始創(chuàng)建這整個(gè)查詢，如下所示。

1. 創(chuàng)建一個(gè)新的查詢【數(shù)據(jù)】【獲取數(shù)據(jù)】【自其他源】【空白查詢】。

2. 將查詢重命名為 “Certificates”。

3. 在公式欄中輸入以下內(nèi)容：

=Excel.CurrentWorkbook()

此時(shí)可以看到表格列表，而且是利用在前幾章學(xué)到的技巧，用戶可以單擊 “Content” 列中 “Table” 單詞旁邊的空白處來預(yù)覽數(shù)據(jù)，如圖 8-15 所示。

圖 8-15 預(yù)覽 “Jan_2008” 表內(nèi)的記錄

如果仔細(xì)觀察 “Content” 列的右上角，會(huì)發(fā)現(xiàn)它有一個(gè)圖標(biāo)，看起來像兩個(gè)指向不同方向的箭頭。這是一個(gè)很酷的功能，本質(zhì)上允許用戶【展開】每一個(gè)表，所有的操作都是一次性完成的。這個(gè)功能被稱為擴(kuò)展操作，最有價(jià)值的地方是，因?yàn)?“Name” 適用于表 “Content” 列中的每一行，展開后它將與此前對(duì)應(yīng)的每一行相關(guān)聯(lián)。

按如下所示進(jìn)行操作。

1. 單擊 “展開” 箭頭，展開 “Content” 列。

2. 取消勾選【使用原始列名作為前綴】的復(fù)選框【確定】。

數(shù)據(jù)很好地展開了，保持了 “Name” 列的細(xì)節(jié)，如圖 8-16 所示。

圖 8-16 子表已經(jīng)被【展開】

【注意】

請(qǐng)記住，列名和數(shù)據(jù)將根據(jù)上一節(jié)中所涉及的規(guī)則進(jìn)行展開，所以，如果此時(shí)列命名不一致，則會(huì)看到一些列中有空值。

要做的下一件事是將 “Name” 列轉(zhuǎn)換為有效的月末日期列。由于 “Jan_2008” 不是一個(gè)有效的日期，需要要用一個(gè)小技巧把它變成一個(gè)有效的日期，然后再更改成月末日期。

1. 右擊 “Name” 列【替換值】。

2. 將 “_” 字符替換為 “ 1 ”（空格 1 空格）。（譯者注：為了構(gòu)成日期格式形態(tài)，為了后續(xù)轉(zhuǎn)換。）

3. 選擇所有列【轉(zhuǎn)換】【檢測(cè)數(shù)據(jù)類型】。

4. 選擇 “Name” 列，轉(zhuǎn)到【轉(zhuǎn)換】標(biāo)簽【日期】【月份】【月份結(jié)束值】。

5. 右擊 “Name” 列【重命名】“Month End”。

現(xiàn)在，完成的查詢將看起來如圖 8-17 所示。

圖 8-17 完成查詢，準(zhǔn)備就緒

這里一切看起來都很好，然而當(dāng)選擇【關(guān)閉并上載】時(shí)，會(huì)看到觸發(fā)了一個(gè)錯(cuò)誤，這很奇怪。單擊查詢旁邊的刷新按鈕，會(huì)看到錯(cuò)誤的數(shù)量發(fā)生了變化，錯(cuò)誤增加到了 63 個(gè)如圖 8-18 所示，這是什么原因？

圖 8-18 “63 個(gè)錯(cuò)誤”？但它看起來如此之好

那么發(fā)生了什么？回去檢查這個(gè)查詢。但在這之前，請(qǐng)確保將 “Certificates” 工作表移動(dòng)到工作簿的最后，如圖 8-19 所示。

圖 8-19 Certificates 工作表，現(xiàn)在是選項(xiàng)卡順序中的最后一個(gè)

【注意】

通常情況下，由于有點(diǎn)麻煩可以不用移動(dòng)這個(gè)工作表，但這有助于確保用戶與本書在這里相同位置看到錯(cuò)誤。

移動(dòng)工作表之后，右擊 “Certificates” 查詢【編輯】，選擇 “Source” 步驟。

此時(shí)，將會(huì)注意到比之前多列出了一個(gè)表，作為這個(gè)查詢的輸出而創(chuàng)建的 “Certificates” 表，如圖 8-20 所示。

圖 8-20 新查詢顯示在所有工作簿查詢的列表中

【注意】

如果在選擇 “Source” 步驟時(shí)沒有看到 “Certificates” 表，那因?yàn)?Power Query 已經(jīng)緩存了數(shù)據(jù)預(yù)覽?？梢酝ㄟ^進(jìn)入【主頁】【刷新預(yù)覽】來解決這個(gè)問題，事實(shí)上，由于緩存的問題，在調(diào)試查詢時(shí)，總是應(yīng)該刷新。

【警告】

當(dāng)使用 “=Excel.CurrentWorkbook ()” 來列舉表或范圍時(shí)，輸出的查詢?cè)谒⑿聲r(shí)也會(huì)被識(shí)別，為了處理這個(gè)問題，需要一些新的步驟，有不同的方式，這取決于用戶如何構(gòu)建查詢。

現(xiàn)在應(yīng)該逐步執(zhí)行查詢的每個(gè)步驟，查看發(fā)生了什么。

當(dāng)進(jìn)入 “Replaced Value（替換的值）” 步驟時(shí)，是否注意到這里有什么危險(xiǎn)的事情發(fā)生，如圖 8-21 所示。

圖 8-21 假設(shè)下一步是將 “Name” 列轉(zhuǎn)換為日期

接下來是檢查 “Changed Types” 步驟，它試圖將 “Name” 列中的所有數(shù)據(jù)類型轉(zhuǎn)換為【日期】類型，但這顯然不能用于 “Certificates” 數(shù)據(jù)。相反，這導(dǎo)致每個(gè)包含該文本的單元格會(huì)產(chǎn)生一個(gè) “Error” 值，如圖 8-22 所示。

圖 8-22 將無效日期轉(zhuǎn)換為錯(cuò)誤

這個(gè)問題實(shí)際上是有利的，因?yàn)楹喜⒑蟮亩Y品券全表中的所有數(shù)據(jù)都是重復(fù)的。對(duì)這些拋出錯(cuò)誤的行，可以簡(jiǎn)單地把它們篩選掉。

1. 確保 “Changed Types” 步驟被選中。

2. 選擇 “Name” 列【主頁】【刪除行】【刪除錯(cuò)誤】。

3. 彈出的對(duì)話框【插入步驟】，單擊【插入】。

4. 轉(zhuǎn)到【主頁】【關(guān)閉并上載】。

完成篩選后，會(huì)從 Power Query 中得到一個(gè)正面的結(jié)果，只加載 62 行數(shù)據(jù)，沒有任何錯(cuò)誤，如圖 8-23 所示。

圖 8-23 從 3 個(gè)合并的表中加載 62 行數(shù)據(jù)

這個(gè)解決方案現(xiàn)在應(yīng)該工作得很好，因?yàn)樗尤肓吮砻裱?“月_年” 格式的任何新表，但篩選掉了任何其他表。唯一的挑戰(zhàn)是什么？現(xiàn)在要依靠記賬員來記住正確命名這些表。鑒于它不是最明顯的元素，這可能是危險(xiǎn)的。

8.3.2 合并區(qū)域或工作表

現(xiàn)在，如果工作表沒有表，而是由職員命名工作表呢，會(huì)怎么樣呢？可以合并所有的工作表嗎？是可以的，但正如第 6 章所提到的，沒有內(nèi)置函數(shù)可以從活動(dòng)工作簿中的工作表中讀取數(shù)據(jù)。相反，必須利用與命名范圍對(duì)話的能力。一個(gè)特定的命名范圍。訣竅是定義一個(gè) “打印區(qū)域”，因?yàn)樗幸粋€(gè)動(dòng)態(tài)名稱，可以通 “Excel.CurrentWorkbook ()” 公式枚舉到這個(gè)名稱。

1. 選擇 “Jan 2008” 工作表，進(jìn)入【頁面布局】選項(xiàng)卡【打印標(biāo)題】。

2. 在【打印區(qū)域】框中輸入：“A:D”【確定】。

3. 對(duì) “Feb 2008” 和 “Mar 2008” 工作表重復(fù)這一過程。

4. 創(chuàng)建一個(gè)新的查詢【自其他源】【空白查詢】。

5. 將該查詢重命名為 “FromWorksheets”。

6. 在公式欄中輸入以下內(nèi)容：

= Excel.CurrentWorkbook()

現(xiàn)在會(huì)看到所有的表格和命名范圍的列表，包括 “打印區(qū)域”，如圖 8-24 所示。

圖 8-24 使用 Excel.CurrentWorkbook 函數(shù)顯示 “打印區(qū)域”

由于目前有兩個(gè)表格和打印區(qū)域，現(xiàn)在來篩選并展開它，看看可以得到什么。

1. 篩選 “Name” 列【文本篩選器】【結(jié)尾為】“Print_Area”【確定】。

2. 將 “Name” 列中的 “'!Print_Area” 文字替換為空白（【替換為】不輸入任何東西）。

3. 將 “Name” 列中剩余的文本（“'”）替換為空。

4. 展開 “Content” 列（取消勾選【使用原始列名作為前綴】復(fù)選框）。

注意，這里的情況有所不同。此時(shí)已經(jīng)成功地創(chuàng)建了一個(gè)從工作表中讀取數(shù)據(jù)的 “黑科技”，在 “打印區(qū)域” 中讀取每一列，如圖 8-25 所示。

圖 8-25 原始的工作表

這顯然意味著需要進(jìn)行更多的數(shù)據(jù)清理，以便匯總這些范圍并將其轉(zhuǎn)換成干凈的表格，但好消息是可以做到這一點(diǎn)。

需要注意的是，在應(yīng)用這種技巧的場(chǎng)景中，將第一行提升為標(biāo)題是有風(fēng)險(xiǎn)的，因?yàn)槿绻腥瞬魂P(guān)心日期列，他們可能會(huì)刪除 “Feb 2008” 這一列，這就會(huì)導(dǎo)致出錯(cuò)。出于這個(gè)原因，這里采用手動(dòng)重命名列的方法，通過設(shè)置數(shù)據(jù)類型觸發(fā)錯(cuò)誤，然后再將這些錯(cuò)誤篩選掉。

因此，清理這個(gè)特定數(shù)據(jù)集的步驟如下所示。

1. 刪除 “Column4”（因?yàn)樗强盏模?/p>

2. 將列重命名為 “Certificate”、“Value”、“Service” 和 “Month End”。

3. 右擊 “Month End” 列【替換值】，在【要查找的值】下面輸入一個(gè)空格，【替換為】輸入 “1,”。（譯者注：沒錯(cuò)，是 “1,”，而不是 1。）

4. 設(shè)置 “Certificate” 列的數(shù)據(jù)類型【整數(shù)】。

5. 設(shè)置 “Value” 列的數(shù)據(jù)類型【整數(shù)】。

6. 設(shè)置 “Service” 列的數(shù)據(jù)類型【文本】。

7. 設(shè)置 “Month End” 列的數(shù)據(jù)類型【日期】。

8. 選擇所有列并轉(zhuǎn)到【主頁】【刪除行】【刪除錯(cuò)誤】。

9. 篩選 “Certificate” 列，取消勾選 “(null)” 值。

10. 選擇 “Month End” 列【轉(zhuǎn)換】【日期】【月份】【月份結(jié)束值】。

11. 轉(zhuǎn)到【主頁】【關(guān)閉并上載】。

完成后，會(huì)發(fā)現(xiàn)它提供的行數(shù)（以及數(shù)據(jù)）與之前構(gòu)建的 “Certificate” 查詢結(jié)果完全相同，如圖 8-26 所示。

圖 8-26 兩種方法，同樣的結(jié)果

在處理 “打印區(qū)域” 時(shí)，盡量將 “打印區(qū)域” 限制在所需要的行和列，這是一個(gè)很好的建議，原因有二：第一是更多的數(shù)據(jù)需要 Power Query 處理的時(shí)間更長(zhǎng)；第二是每一列在處理后會(huì)自動(dòng)形成一推形如 “Column#” 的列，導(dǎo)致很多無意義的空列會(huì)被納入進(jìn)來，還需要再刪除。

8.3.3 Excel.CurrentWorkbook

在使用 Excel.CurrentWorkbook 函數(shù)構(gòu)建解決方案時(shí)，需要記住的最重要的一點(diǎn)是這個(gè)函數(shù)會(huì)讀取當(dāng)前文件中的所有對(duì)象。由于這會(huì)影響計(jì)算鏈，所以會(huì)受到遞歸效應(yīng)的影響，這意味著隨著新表的構(gòu)建，Power Query 會(huì)識(shí)別它們并將它們也作為潛在的內(nèi)容來讀取。

當(dāng)查詢?cè)噲D加載自身時(shí)，這種情況會(huì)在刷新時(shí)出現(xiàn)，從而在輸出中重復(fù)了數(shù)據(jù)。當(dāng)使用這種方法時(shí)，重要的是記住這一點(diǎn)并加以防范。

在這里，防止出現(xiàn)問題的策略包括篩選關(guān)鍵列上的錯(cuò)誤，以及為輸入和輸出列使用標(biāo)準(zhǔn)命名，從而篩選掉不需要的列。

【注意】

無論用戶選擇哪種方法，請(qǐng)確保在將其發(fā)布到生產(chǎn)環(huán)境之前通過刷新進(jìn)行多次測(cè)試。

8.4 關(guān)于追加查詢的最后思考

本章講述的功能意義重大，假設(shè)用戶有三個(gè)獨(dú)立的文件，導(dǎo)入并將它們合并到一個(gè)單一的 “Transactions” 表中，并基于這些數(shù)據(jù)建立一個(gè)【數(shù)據(jù)透視表】或 Power BI 可視化。這就是一個(gè)基于三個(gè)獨(dú)立文件的商業(yè)智能解決方案。

而當(dāng)用戶想刷新這個(gè)解決方案時(shí)，只需要單擊【全部刷新】按鈕就可以更新它。Power Query 將啟動(dòng)對(duì) “Transactions” 表的刷新，這將啟動(dòng)對(duì)三個(gè)單獨(dú)的數(shù)據(jù)表的刷新，為它提供數(shù)據(jù)。

假設(shè)現(xiàn)在這個(gè)解決方案是建立在沒有特定日期的文件上，而它們是 “Product 1、Product 2 和 Product 3”。用戶已經(jīng)通過加載 “CSV” 文件構(gòu)建了解決方案，這些文件包含了相關(guān)的數(shù)據(jù)，并針對(duì)它們建立了商業(yè)智能報(bào)告。然后，下個(gè)月來了，IT 部門給分析師發(fā)送了替換文件，為每個(gè)產(chǎn)品提供新的交易數(shù)據(jù)。

用戶把新的 “Product 1” 文件覆蓋到舊的文件上，對(duì) “Product 2” 和 “Product 3” 做同樣的處理。然后單擊【全部刷新】，此時(shí)就已經(jīng)完成了。

沒錯(cuò)，只需要點(diǎn)擊刷新，一切就完成了。

另外，追加查詢的功能不僅能用于處理外部文件，也可以將當(dāng)前工作簿中的所有表格或打印區(qū)域結(jié)合起來合并，創(chuàng)建一個(gè)用于分析的表。

因?yàn)?Power Query 的縱向追加數(shù)據(jù)功能，原有的工作時(shí)間被大幅縮短，并且不存在用戶意外地復(fù)制粘貼數(shù)據(jù)導(dǎo)致數(shù)據(jù)重復(fù)的風(fēng)險(xiǎn)，這里根本不需要復(fù)制粘貼，只需要將一組數(shù)據(jù)追加到另一組，刪除重復(fù)的標(biāo)題。這種方式，可以構(gòu)建同時(shí)擁有了速度和一致性兩重優(yōu)點(diǎn)的解決方案。

至此，已經(jīng)探索了用外部數(shù)據(jù)源的手動(dòng)追加，以及如何為工作簿中的數(shù)據(jù)生成自動(dòng)更新系統(tǒng)，有沒有可能把這些合并起來，創(chuàng)建一個(gè)系統(tǒng)，可以推廣到合并一個(gè)文件夾中的所有文件，而不必在 Power Query 中手動(dòng)添加每個(gè)文件？答案是肯定的，將在第 9 章繼續(xù)討論這個(gè)問題。

正在學(xué)習(xí) Power Query 嗎？可以加入本主題的交流群一些交流分享。

Power Query 真經(jīng)連載

Power BI 終極系列課程《BI真經(jīng)》

BI真經(jīng) - 讓數(shù)據(jù)真正成為你的力量

掃碼與精英一起討論 Power BI，驗(yàn)證碼：data2022
掃碼與精英一起學(xué)習(xí) Power Query，驗(yàn)證碼：PQ真經(jīng)

點(diǎn)擊“閱讀原文”進(jìn)入學(xué)習(xí)中心

↙