實驗 01 - 在 Excel 中使用 Power Query

已完成

現在, Power Query 您有機會嘗試使用此引導式分步用例。 使用提供的範例數據源檔完成練習。

實驗 01 - Excel 中的分析:在 Excel 中使用 Power Query

完成此實驗的估計時間為 30 分鐘。

在此動手學習實驗室中,你將完成以下任務:

  1. 用於 Power Query 連線.csv 來源資料檔案 - 客戶
  2. 使用 Power Query 轉換透過分隔符拼接欄 - 客戶
  3. 用於 Power Query 連線到 Excel 來源資料檔案 - 引號
  4. 使用 Power Query 轉換取消透視 - 引號
  5. 使用 Power Query 轉換進行清理 - 引號

實驗室先決條件

必須滿足以下先決條件和設置才能成功完成練習:

  • 您必須連接到互聯網。

  • 您必須已 Microsoft Office 安裝。

  • 註冊 Microsoft Power BI

  • 至少,您應該有一台具有兩個內核和 4 GB RAM 的計算機,該計算機運行 Windows 8 或更高版本或 Windows Server 2008 R2 或更高版本。

  • 您可以使用 Microsoft Edge 或谷歌瀏覽器。

  • 驗證您使用的是 32 位還是 64 位作業系統,以確定是否需要安裝 32 位或 64 位應用程式。

    便條

    64 位 Excel 是 Power BI Desktop 最好的。

    重要

    下載學生內容:在本地計算機的 C:驅動器上創建一個名為 ANALYST-LABS 的資料夾。 從 https://aka.ms/modern-analytics-labs 中下載所有內容並將其提取到您創建的 ANALYST-LABS 資料夾 (C:\ANALYST-LABS)。

  • 使用以下任一選項下載並安裝 Power BI Desktop :

    • 如果您使用的是 Windows 10 或更高版本,請使用 Microsoft App Store 下載並安裝 Power BI Desktop 應用程式。
    • 下載並安裝 Microsoft Power BI 桌面
  • 如果已 Power BI Desktop 安裝,請確保已下載最新版本的 Power BI 。

文件結構

每個實驗室的源數據或起始檔都位於每個實驗室資料夾中。

  • 您將使用 Power Query Excel 應用程式完成實驗 01。

  • 您將使用 Power BI Desktop 應用程式完成實驗 02A 和實驗 02B。

  • 您將透過使用 Power BI DesktopPower BI 服務和 Excel 應用程式完成實驗 03A。

  • 你將使用 Excel 和服務 Power BI 應用程式完成實驗 03B。

每個實驗室都附帶跟隨分步說明,並在整個說明中包含螢幕圖像。 每個步驟的關鍵操作由 粗體 文本標識。 注意筆記、提示和其他重要資訊。 每個實驗室都包含一個完整的解決方案檔,可用作參考。

概觀

完成此實驗的估計時間為 30 分鐘。 在本實驗中,您將完成以下任務:

  1. 用於 Power Query 連線到 CSV 來源資料檔案 - 客戶

  2. 使用 Power Query 轉換按分隔符分割欄 - 客戶

  3. 用於 Power Query 連線到 XLSX 來源資料檔案 - 引號

  4. 使用 Power Query 轉換取消透視 - 引號

  5. 使用 Power Query 轉換清理 - 引號

    便條

    該實驗室是根據虛構 的 Wi-Fi 公司的銷售活動 SureWi創建的,該公司由 P3 Adaptive 提供。 該數據是 P3 自適應的屬性,共用的目的是使用行業示例數據演示 Excel 和 Power BI 功能。 對這些數據的任何使用都必須包括對 P3 自適應的歸屬。

練習 1:用於 Power Query 連線到 CSV - Customers.csv

在本練習中,您將使用 Excel 連線到 CSV 源資料檔。

工作 1:啟動 Excel

在本工作中,您將啟動一個新的空白工作表以開始使用。

  1. 啟動 Excel。

    Excel 徽標的屏幕截圖。

  2. 創建一個新的空白工作簿。

    Excel 中新建空白工作簿選擇螢幕的屏幕截圖。

工作 2:用於 Power Query 連線到 CSV

在本工作中,您將連線客戶 CSV 源數據檔。

  1. 選擇主 Excel 功能區上的「數據 選項卡。

  2. 選擇 從文字/CSV > 從檔案 > 中獲取數據

    “來自文本/CSV 的數據 > ”功能表選項的屏幕截圖。

  3. 轉到 C:\ANALYST-LABS\Lab 01\MAIAD 實驗 01 - 資料來源 - Customers.csv 檔。

    預覽版 區域將顯示客戶資料、列名稱和值的範例。

    便條

    此示例只是數據的預覽版。

    “導航”視窗的屏幕截圖,顯示實驗 01 - 數據源 - Customers.csv 中的預覽版數據。

  4. 選擇「 轉換數據 」按鈕。 這將啟動 Power Query 編輯器視窗。

    便條

    使用時 Power Query,最好最大化 Power Query 編輯器視窗,以便可以看到視窗功能表、窗格和選項的完整檢視 Power Query 。

  5. 默認情況下,「 編輯器」視窗左側 的「查詢 Power Query 」窗格將處於摺疊狀態。 選擇「查詢」窗格中的 箭頭 以展開並打開「查詢」窗格。

    編輯器視窗的 Power Query 螢幕截圖,其中「查詢」窗格已摺疊,並顯示箭頭以展開窗格。

  6. 在「查詢 」窗格中,右鍵單擊名為 「MAIAD 實驗室 01 - 數據源 - 客戶 」的預設查詢名稱,然後將 查詢重命名「客戶」。

    “查詢”窗格打開的屏幕截圖,顯示“實驗室 01 - 數據源 - 客戶查詢”,並突出顯示“重命名”按鈕。

    應為用作數據模型一部分的查詢提供一個清晰、描述性、使用者友好的名詞名稱,用於描述數據所表示的內容。 例如,客戶、報價單、發票、產品、地理位置等。

練習 2:使用 Power Query 轉換按分隔符拆分列 - 客戶

在本練習中,您將用於 Power Query 從聯繫人列中提取名字。

工作 1:使用範例中的欄

在本工作中,您將創建一個名為 “名字 ”的新列,方法是使用 “從示例 > 中添加列 列”轉換以分隔符拆分 聯繫人

  1. 從預覽版 格線中,選擇「 聯繫人 」列。

  2. 從“ 添加列 ”選項卡中,選擇 “示例 中的列”向下箭頭,然後選擇“ 從所選內容 ”選項。

    顯示「添加列」功能表選項的編輯器視窗的 Power Query 螢幕截圖。

    便條

    這將打開一個名為 “從示例添加列”的新 使用者介面視窗。此視窗看起來像 Power Query 預覽版格線,但它是單獨的視窗,允許您鍵入建議的值, Power Query 以便可以識別要應用的模式和公式,從而實現最終結果。

  3. 在“ 從示例 添加列”視窗的名為[Column1] 的列中,鍵入值 “雨果” 然後輸入。

    “從示例添加列”視窗的屏幕截圖,顯示 Hugo 在“列 1”中輸入。

    便條

    Enter 鍵後, Power Query 將識別數據中是否存在模式以填充所有行的值。

  4. 按兩下 名為 「分隔符前的文字」的預設標題, 然後將新列重命名為 「名字」。 選擇確定 按鈕。

    “示例中的列”視窗的屏幕截圖,顯示將預設列名稱重命名為“名字”。

    便條

    現在,在 Power Query 編輯器預覽版格線中,您會注意到名為 “名字”的 NEW 列,該列是通過使用“示例中的列”轉換解析 [聯繫人] 中的 [名字] 而創建的。

    編輯器中 Power Query 顯示新名字列的預覽版格線的屏幕截圖。

練習 3:用於 Power Query 連線到 XLSX - Quotes.xlsx

在本練習中,您將使用 Excel 連線到 XLSX 源數據檔。

工作 1:從 Power Query 編輯器視窗中連線到 XLSX 源資料

在本工作中,您將從 Power Query 編輯器 視窗中開始。

  1. 從「開始 Power Query 功能表中,選擇「 新建源 > Excel 工作簿 檔」選項。

    編輯器視窗的 Power Query 螢幕截圖,顯示「主頁」選項卡選項,其中選擇了「新建源 > 檔 > Excel 工作簿」選項。

  2. 導航到檔 C:\ANALYST-LABS\Lab 01\Data 源 - Quotes.xlsx

  3. 在“導航”視窗中,選擇名為 “實驗 01 - 引號”的工作表。

    便條

    這隻是 數據的預覽版。

    “導航器”視窗的屏幕截圖,顯示突出顯示的“實驗 01A - 報價”工作表。

  4. 選擇確定 按鈕以在 Power Query 編輯器視窗中作為第二個查詢載入。

  5. 在「查詢 」窗格中,右鍵單擊名為「實驗 01 - 引號」的預設查詢名稱,將 查詢重命名 為「引號」。。

    編輯器視窗中“查詢”窗格 Power Query 的屏幕截圖,其中選擇了“實驗 01 - 引號”名稱進行重命名。

練習 4:用於 Power Query 取消透視 - 引號

在本練習中,您將使用 Power Query 變換來構建其 Power Pivot報價數據。

工作 1:使用第一行作為標題轉換按鈕

在本工作中,您將把包含列標題值的第一行移動到表標題。

在“開始 功能表上,選擇“ 使用第一行作為標題 ”按鈕。

“開始”選項卡的屏幕截圖,顯示選中了“使用第一行作為標題”按鈕。

工作 2:使用「取消透視」轉換功能表選項

在本工作中,您將取消透視報價數據。

  1. 預覽版 窗格中,右鍵單擊 CustID 列以顯示功能表選項。

  2. 然後選擇取消透視其他列 選項。

    CustID 列和“取消透視其他列”選項的屏幕截圖突出顯示以供選擇。

  3. 按兩下 「屬性 」列,然後將其 重命名為 QuoteDate

  4. 按兩下“值” 列,然後將其 重命名為 QuoteAmt

    之前

    重新命名之前的 CustID、屬性和值列標題的屏幕截圖。

    之後

    重命名為 QuoteDate 和 QuoteAmt 的“屬性”和“值”列的屏幕截圖。

練習 5:用於 Power Query 清理 - 引號

在本練習中,您將使用 Power Query 轉換來清理報價數據。

工作 1:使用取代轉換

在本工作中,您將使用替換技術將 QuoteDate 更改為可轉換為 Date 資料類型的完整日期。

  1. 在預覽版視窗中,右鍵單擊 「報價日期 」列以顯示功能表選項。

  2. 接下來,選擇 替換值... 選項。

    突出顯示「替換值」功能表選項的「報價日期」列的屏幕截圖。

  3. 在「替換值 UI」 視窗中:

    1. 在要 - 查找 的值文字框中輸入連字元

    2. 在替換為文字框中輸入 /1/

    “替換值”視窗的屏幕截圖,顯示“要查找的值”和“替換為”文本框中的條目。

    1. 選擇確定 按鈕。

工作 2:使用「資料類型」圖示

在本工作中,您將使用「資料類型」圖示將資料類型從“文本”更改為“日期”。

  1. 選擇 QuoteDate 列標題中的 ABC 圖示,指示該列為文字數據類型。

  2. 從數據類型功能表選項中選擇日期 選項。

    已選取報價日期資料格式圖示的螢幕截圖。

工作 3:關閉並載入到資料模型

在本工作中,您將「客戶」和「報價」表載入到數據模型中。

  1. 從“主頁 功能表中,選擇 “關閉”&“載入 > ”“關閉”&“載入到...”。

    選擇了「關閉並載入」按鈕的螢幕截圖,並突出顯示了「關閉並載入到」選項。

  2. 在「匯入」視窗中,選擇「 僅創建連接」 單選按鈕。

    “導入數據”視窗的屏幕截圖,顯示“僅創建連接”按鈕,並選中“將此數據添加到數據模型”複選框。

  3. 選中將 此數據添加到數據模型旁邊的框。

  4. 選擇確定 按鈕。

    便條

    載入的表將顯示在「查詢 &; 連接窗格」視窗中,並載入總行數。

    “查詢 &; 連接”窗格的屏幕截圖,其中顯示載入了 7,560 行的客戶查詢和載入了 84,307 行的報價查詢。

    便條

    在此指向,我們已使用 Power Query 並選中了複選框選項以 將此數據添加到數據模型。 但是,我們實際上還沒有看到這些數據的載入位置。 在實驗 02A 中,我們將使用 Power BI Desktop 導入 Power Query 連接、客戶表和報價表來創建數據模型。

    便條

    若要編輯“查詢”並重新啟動 Power Query “編輯器”視窗,請選擇 “數據>查詢和連接 ”以顯示“查詢和連接”窗格。

    “數據”選項卡下的“查詢和連接”按鈕的屏幕截圖。

    便條

    當您選擇 關閉並載入到... 並首次設置「導入數據」選項,這是預設設置。 您始終可以編輯這些設定,方法是右鍵單擊「查詢 & 連接」窗格中的 「查詢 」,然後選擇 「載入到...“ 。選項以顯示和更新導入數據設置。

    “查詢和連接”窗格中某個專案的上下文對話框中“載入到”選項的屏幕截圖。

工作 4:儲存檔案

在本工作中,您將保存包含「 客戶 」和 「報價 」查詢連接的 Excel 檔。

  1. 從主 Excel 功能區中,選擇“ 檔 > 保存”

  2. 轉到 C:\ANALYST-LABS\Lab 01 資料夾,然後將檔另存為 實驗室 01 - 我的 Solution.xlsx

摘要

在本實驗中,你在 Power Query Excel 中使用連線到 CSV 和 XLSX 源數據檔,使用“示例中的列”創建新列,取消透視並應用轉換 Power Query,將源數據載入到數據模型,並使用數據連接保存 Excel 檔。

Excel 中顯示「客戶」和「報價」查詢的最終結果的屏幕截圖。