探索 Excel 的獲取和轉換功能

執行摘要

在這個數據湖和 PB 級數據庫的時代，我仍然以 CSV、文本和 Excel 文件形式接收數據的頻率令人震驚。 儘管現代分析側重於機器學習算法的前沿進展，但數據分析的日常苦差事仍然是查找、編譯和整理不同數據類型的手動過程。

對於財務分析師來說，數據通常以 Excel 電子表格的形式出現，但同樣經常的是，它是將數據轉儲到 CSV 或查詢到 SQL 數據庫。 有時，數據排列在混亂的佈局中，或者沒有所有必要的分析組件。 花在清理這些數據上的時間對分析師來說是浪費的寶貴時間，但有時這項任務被認為是必須容忍的。

獲取和轉換做什麼？

這個常見問題的解決方案實際上很容易獲得：Excel 和 Power BI 擁有一整套很少有用戶知道的數據轉換工具，名為 Get & Transform（以前稱為 Power Query）。 使用其嵌入式提取、轉換和加載 (ETL) 功能，金融分析師能夠無縫鏈接到他們的數據源並更快地獲得洞察力。

當我們將數據加載到 Excel 或 Power BI 中時，我們通常必須對數據執行一些轉換。 數據操作的一些示例包括：

• 刪除列，
• 過濾數據，
• 分組數據，
• 透視/反透視數據，
• 將字符串拆分為子字符串，
• 從字符串中提取關鍵字，
• 附加來自另一個表的行，以及
• 連接兩個維度表。

在下圖中，我們看到 Get & Transform 執行了在加載數據之前對數據進行預處理的繁瑣任務。

為什麼要使用 Get & Transform？

為什麼值得學習如何使用 Get & Transform？ 好吧，當我查看我個人使用此功能的目的時，它為我提供了一組可塑的工具：

• 將整個文本文件文件夾加載到單個數據表中
• 將導出的會計文件轉換為易於理解的佈局
• 將數百萬個銷售行直接加載到 Power Pivot
• 將每日數據分組為可管理的每月結果，然後將其導入 Excel
• 通過加入匹配的列來拼接來自另一個表的數據

通常，當我收到新數據時，我會在將其加載到 Power Pivot 之前使用 Get & Transform 對其進行探索。 這讓我可以看到可能需要進行哪些轉換，并快速對數據執行一些數據透視和分組，以製定分析框架。 很多時候，在這個階段，我會發現自己需要更多的數據，或者說有數據問題。 通過使用基於 Excel 的平台，我可以快速迭代我的數據源以查找這些數據異常。

最終，是留在 Excel 中還是將數據分析轉移到另一個平台將取決於受眾以及分析的可重複性和分佈。 如果我的客戶只使用 Excel，那麼我幾乎總是使用 Get & Transform 加載數據，使用 Power Pivot 執行分析，使用 Excel 生成數據透視表和圖表。 對於客戶來說，這將是無縫的，因為它都包含在 Excel 中。

但是，如果我的客戶：

1. 想使用另一個可視化工具，
2. 有多個用戶將刷新數據，或者
3. 需要使用機器學習模型，

然後我將僅使用 Get & Transform 進行初始數據探索，然後將繁重的工作轉移到 R 中。

如何在 Excel 或 Power BI 中訪問獲取和轉換

在早期版本的 Excel 中，Power Query 是一個可以安裝以幫助使用 ETL 功能的加載項。 但是，在 Excel 2016 和 Power BI 中，這些工具的集成更加緊密。 在 Excel 2016 中，可以通過“數據”選項卡和“獲取和轉換數據”部分訪問它們。

在 Power BI 中，該功能位於“主頁”選項卡的“外部數據”部分中。

在本文中，我的示例在 Power BI 中進行，但界面幾乎與 Excel 相同。 當它們出現時，我會指出它們之間的差異，因此本教程應該對兩種類型的用戶都有意義。

1. 加載 CSV 文件

為了協助本教程，我為一家銷售戶外裝備和服裝的虛構零售商創建了一些銷售數據示例。 在每個示例中，數據將以不同的方式生成，以演示數據轉儲的實際方法。

作為一個初始示例，我們將看到數據以大型數據轉儲到 CSV 文件的形式呈現。 複雜的因素是數據以代表不同商店的多列呈現。 理想情況下，我們希望將數據導入並轉換為更可用的佈局。

下面是原始 CSV 的截圖：

我們為什麼要改變這一點？ 利用這些應用程序中可能存在的關係功能​​。 我們將在討論中看到這一點。

現在，讓我們假設我們需要將數據視為“更窄和更高”的結構，而不是“更寬和更短”的結構。 第一步是加載 CSV； 然後，我們將開始“反透視”數據。

如您所見，數據的最終結構比初始數據要窄，而且要長得多。 另一點是，當我們單擊不同的操作時，右側的工具正在生成用於構建查詢的應用步驟列表。 重要的是要了解這是在後台進行的，因為稍後將對其進行重新訪問。

在大多數情況下，Power BI 和 Excel 之間的 Get & Transform 外觀和行為相似。 但是，在 Excel 中，單擊Close and Load後，會出現一個附加提示。 在下圖中，我們可以在是否要加載數據之間切換：

1. Excel中的表格，
2. 針對數據創建的數據透視表，
3. 針對數據創建的數據透視圖，或
4. “只創建一個連接。”

此外，我們還可以選擇是否將此數據添加到數據模型中。 選中此框會將數據加載到 Power Pivot 表中。 如果我們要分析 Power Pivot 中的數據，我建議選擇“僅創建連接” ，然後確保選中“將此數據添加到數據模型”選項。 如果數據在 Excel 行限制內，並且我們更喜歡在 Excel 中進行分析，那麼只需選擇Table 。

在下一個剪輯中，我們將看到我們將數據格式化為長而細的原因是為了我們不僅可以按商店分析銷售額，還可以按地區和州分析銷售額。 為了完成這個任務，我們將導入一個將每個商店映射到一個區域和狀態的表。 我們將在下面看到，我們可以快速創建按這些不同分組顯示銷售額的報告。

您可以想像 Excel 或 Power BI 中的這種類型的數據轉換功能如何強大地應用於我們擁有動態數據分組的任何情況，例如：

• 將每日數據匯總為周、月和季度；
• 將銷售人員分組到部門和區域； 要么
• 將 SKU 映射到產品類型。

雖然本文介紹了 CSV 和其他 Excel 文件，但 Get & Transform 處理了廣泛的數據類型。 創建查詢後，它可以隨著數據的變化而隨著時間的推移而刷新。

2.處理文本字符串

為了展示 Get & Transform 處理字符串的能力，我創建了另一個數據集，該數據集模擬了一個文本文件，顯示了來自公司總賬 (GL) 的會計交易。

請注意帳號和名稱如何出現在同一個字符串中？ 在 Power BI 中，我們可以毫不費力地將帳號和名稱解析為單獨的字段。

在此視頻中，您可以看到，在我拆分列後，該工具猜測 Account 字段的新左側應該是一個數字，並創建了一個“Changed Type1”步驟。 由於我們最終希望此字段為字符串，因此我們可以繼續並在應用的步驟下手動刪除該步驟。

接下來，我們採用相同的數據並創建一個會計科目表，其中包含到科目類別的映射。

為什麼我們要通過所有這些步驟來映射一些帳號？ 一個真正的總賬可以是數百甚至數千個賬戶。 正如我們所展示的，這個快速映射查詢無需額外工作即可擴展到該級別。

3. 使用不同的數據源

Get & Transform 支持許多不同的數據源。 雖然不是一個詳盡的列表，但以下是一些示例：

就個人而言，我只嘗試了上述列表中大約一半的連接。 我使用的每個連接器都相當堅固。 我無需大量工作即可從原始數據獲得洞察力。 同樣重要的是，它充當不同數據源之間的驗證器，確保最終輸出具有標準化的質量控制水平。

4. 使用 M 語言個性化代碼

在後台，每次我們單擊工具中的按鈕或進行選擇時，Get & Transform 都會生成代碼。 下面是一個示例，說明您將如何訪問我們創建的帳戶映射查詢的代碼：

該代碼使用一種名為 M 的函數式語言，它會為基本用例自動生成。 但是，對於更複雜的數據整理，我們可以編輯和編寫自己的代碼。 在大多數情況下，我只會對這段代碼進行微小的修改。 在更複雜的轉換中，我可能會從頭開始編寫大部分代碼來暫存臨時表，或者執行更複雜的連接。

Get & Transform 的極限

當您嘗試導出超過一百萬行時，Excel 往往會達到其極限。 在我使用 Get & Transform 轉換了數百萬行的情況下，發送未分組行的唯一方法是通過乏味的 hack 或解決方法。 我還發現 Get & Transform 查詢在部署到多個用戶時可能不穩定，尤其是在您使用多個數據源和聯接的情況下。 在這些情況下，我將始終使用 R 來部署可複制的數據整理。 最後，Excel 不是為更高級的數據建模而構建的。 您可以非常快速地執行線性回歸，但除此之外，您將需要使用更嚴格的平台。

說了這麼多，我發現 Excel 是我的大多數客戶最喜歡的。 Excel 仍然是金融分析師武器庫中最重要的工具。 通過合併 Get & Transform 功能，Excel 和 Power BI 通過它們可以接受的數據源範圍變得更加強大。