實驗室 01 - 在 Excel 中使用 Power Query

已完成

現在,您有機會 Power Query 試用本指南的逐步使用案例。 請使用所提供的範例資料來源檔案,以完成練習。

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

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

在此實際操作學習實驗室中,您將完成下列工作:

  1. 使用 Power Query 連線 .csv 來源資料檔案 - Customers
  2. 使用 Power Query 轉換,依分隔符號來分割資料行 - Customers
  3. 使用 Power Query 連線 Excel 來源資料檔案 - Quotes
  4. 使用 Power Query 轉換來取消樞紐 - Quotes
  5. 使用 Power Query 轉換來進行清理 - Quotes

實驗室必要條件

必須備妥下列必要條件和設定,才能順利完成練習:

  • 您必須連線至網際網路。

  • 您必須安裝 Microsoft Office。

  • 註冊 Microsoft Power BI

  • 您至少應該有一部具有兩個核心和 4 GB RAM 的計算機,其執行 Windows 8 或更新版本或 Windows Server 2008 R2 或更新版本。

  • 您可以使用 Microsoft Edge 或 Google Chrome。

  • 請確認您是否有 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 應用程式。
    • 下載並安裝 Power BI Desktop
  • 如果您已經安裝 Power BI Desktop,請確定您已下載最新版本的 Power BI。

文件結構

每個實驗室的來源資料或起始檔案,都位於每個實驗室資料夾內。

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

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

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

  • 您將使用 ExcelPower BI 服務 應用程式完成實驗室 03B。

每個實驗室都有逐步指示可遵循,且指示中包含著螢幕影像。 每個步驟的主要動作都是以粗體文字來識別。 請注意備註、提示和其他重要資訊。 每個實驗室都包含一個完整的解決方案檔,您可以做為參考。

概觀

完成此實驗室的估計時間為 30 分鐘。 在本實驗室中,您將完成下列工作:

  1. 使用 Power Query 連線至 CSV 來源資料檔案 - Customers

  2. 使用 Power Query 轉換,依分隔符號來分割資料行 - Customers

  3. 使用 Power Query 連線至 XLSX 來源資料檔案 - Quotes

  4. 使用 Power Query 轉換來取消樞紐 – Quotes

  5. 使用 Power Query 轉換來進行清理 – Quotes

    注意

    本實驗室是根據一家名為 SureWi 的「虛構」Wi-Fi 公司,使用 P3 Adaptive 來進行銷售活動而建立的。 該資料是 P3 Adaptive 的資產,並且已和用於示範的 Excel 及內含產業範例資料的 Power BI 功能一起共用。 任何使用此資料的用途,都必須歸功於 P3 Adaptive 的這項功勞。

練習 1:使用 Power Query 連線至 CSV - Customers.csv

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

工作 1:啟動 Excel

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

  1. 啟動 Excel。

    Excel 標誌的螢幕快照。

  2. 建立新的空白活頁簿。

    Excel 中 [新增空白活頁簿] 選取畫面的螢幕擷取畫面。

工作 2:使用 Power Query 連線至 CSV

在這項工作中,您將連線到 Customers CSV 源數據檔。

  1. 選取主要 Excel 功能區上的 [資料] 索引標籤。

  2. 從文字/CSV 選取 [從檔案>取得數據>]。

    [來自文字/CSV 的數據] > 選單選項的螢幕快照。

  3. 移至 C:\ANALYST-LABS\Lab 01\MAIAD Lab 01 - Data Source - Customers.csv 檔案。

    [預覽] 區域會顯示客戶資料、資料行名稱和值的範例。

    注意

    此範例只是資料的預覽。

    [導覽器] 視窗的螢幕快照,其中顯示實驗室 01 - 數據源 - Customers.csv 的預覽數據。

  4. 選取 [轉換資料] 按鈕。 這會啟動 [Power Query 編輯器] 視窗。

    注意

    當您在 Power Query 中工作時,最好將 Power Query 編輯器 視窗最大化,讓您可以查看 Power Query 視窗功能表、窗格和選項的完整檢視。

  5. 根據預設,Power Query 編輯器視窗左側的 [查詢] 窗格將會摺疊。 選取 [查詢] 窗格中的 箭號 ,展開並開啟 [查詢] 窗格。

    [Power Query 編輯器] 視窗的螢幕擷取畫面,其中 [查詢] 窗格已摺疊,並顯示展開窗格的箭號。

  6. 在 [查詢] 窗格中,以滑鼠右鍵按一下名為 [MAIAD Lab 01 - Data Source - Customers] 的預設查詢名稱,然後將查詢重新命名Customers

    開啟 [查詢] 窗格的螢幕擷取畫面,其中顯示 Lab 01 - Data Source - Customers 查詢,並醒目提示 [重新命名] 按鈕。

    提示

    您作為數據模型的一部分使用的查詢,應該提供清楚、描述性、用戶易記的名詞名稱,以描述數據所代表的內容。 例如,客戶、報價、發票、產品、地理位置等等。

練習 2:使用 Power Query 轉換,依分隔符號分割資料行 - Customers

在此練習中,您將使用 Power Query 從 [聯繫人] 數據行擷取 [名字]。

工作 1:使用範例中的資料行

在此工作中,您將使用 [從範例新增>數據行] 轉換來分割聯繫人,以建立名為名字的新數據行。

  1. 在 [預覽] 方格中,選取 [Contact] 資料行。

  2. 從 [ 新增數據行] 索引卷標中, 從 [範例 ] 向下箭號選取 [數據行],然後選取 [ 從選取範圍 ] 選項。

    顯示 [新增資料行] 功能表選項的 [Power Query 編輯器] 視窗螢幕擷取畫面。

    注意

    這會開啟名為「從範例新增數據行」的新使用者介面視窗。此視窗看起來像 [預覽] 方格 Power Query,但它是個別的視窗,可讓您輸入建議的值,讓 Power Query 可以識別可套用最終結果的模式和公式。

  3. 在 [Add Column From Examples] (從範例新增資料行) 視窗的 [Column1] 資料行中,鍵入 "Hugo" 值,然後按下 Enter。

    [Add Column From Examples] (從範例新增資料行) 視窗的螢幕擷取畫面,其中顯示在 Column1 中輸入的 Hugo。

    注意

    按下 Enter 鍵之後,Power Query 會識別數據中是否有模式存在,以填入所有數據列的值。

  4. 按兩下名為 [分隔符號之前文字] 的預設標頭,然後將新資料行重新命名為 "First Name"。 選取 [確定] 按鈕。

    [來自範例的資料行] 視窗的螢幕擷取畫面,其中顯示將預設資料行名稱重新命名為 First Name。

    注意

    現在,在 [Power Query 編輯器 預覽] 方格中,您會注意到名為 [名字] 的新數據行,其建立方式是使用 [來自範例轉換的數據行] 剖析 [名字] 。

    Power Query 編輯器內 [預覽] 方格的螢幕擷取畫面,其中顯示新的 [First Name] 資料行。

練習 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 Source - Quotes.xlsx 檔案。

  3. 在 [導覽器] 視窗中,選取名為 "Lab 01 - Quotes" 的工作表。

    注意

    這隻是數據的預覽。

    [導覽器] 視窗的螢幕擷取畫面,其中顯示醒目提示的 Lab 01A - Quotes 工作表。

  4. 選取 [確定] 按鈕,以載入為 [Power Query 編輯器] 視窗中的第二個查詢。

  5. 在 [ 查詢] 窗格中,以滑鼠右鍵按兩下名為 「Lab 01 - Quotes」 的預設查詢名稱,將查詢 重新命名 為 「Quotes」。。

    [Power Query 編輯器] 視窗中 [查詢] 窗格的螢幕擷取畫面,其中已選取 Lab 01 - Quotes 名稱,以重新命名。

練習 4:使用 Power Query 取消樞紐 - Quotes

在此練習中,您將使用 Power Query 轉換來建構 Power Pivot 的報價數據。

工作 1:使用第一個資料列作為標頭轉換按鈕

在這項工作中,您會將具有數據行行首值的第一個數據列移至數據表標頭。

在 [首頁] 功能表上,選取 [使用第一個資料列做為標頭] 按鈕。

[首頁] 索引標籤的螢幕擷取畫面,其中顯示已選取 [使用第一個資料列做為標頭] 按鈕。

工作 2:使用 [Unpivot transformation] (取消樞紐轉換) 功能表選項

在這項工作中,您將取消樞紐引述數據。

  1. [預覽] 窗格中,以滑鼠右鍵單擊 [CustID ] 數據行以顯示功能表選項。

  2. 然後選擇 [取消其他資料行樞紐] 選項。

    [CustID] 資料行的螢幕擷取畫面,並且已醒目提示 [取消其他資料行樞紐] 選項以供選取。

  3. 按兩下 [屬性] 資料 行,然後將它重新命名為 QuoteDate

  4. 按兩下 [值 ] 資料行,然後將它重新命名為 QuoteAmt

    之前

    [CustID]、[Attribute] 和 [Value] 資料行標頭重新命名之前的螢幕擷取畫面。

    之後

    分別重新命名為 [QuoteDate] 和 [QuoteAmt] 之 [Attribute] 和 [Value] 資料行的螢幕擷取畫面。

練習 5:使用 Power Query 進行清理 - Quotes

在此練習中,您將使用 Power Query 轉換來清除引號數據。

工作 1:使用取代轉換

在這項工作中,您將使用 replace 技術,將 QuoteDate 變更為可轉換成 Date 數據類型的完整日期。

  1. 在 [預覽] 視窗中,以滑鼠右鍵按兩下 QuoteDate 資料 行以顯示選單選項。

  2. 接下來,選擇 [取代值...] 選項。

    醒目提示 [取代值] 功能表選項之 [QuoteDate] 資料行的螢幕擷取畫面。

  3. 在 [ 取代值 UI] 視窗中:

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

    2. 在 [取代為] 文字框中輸入 /1/

    [取代值] 視窗的螢幕擷取畫面,其中顯示 [要尋找的值] 和 [取代為] 文字方塊中的輸入。

    1. 選取 [確定] 按鈕。

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

在此工作中,您將使用 [數據類型] 圖示,將數據類型從 [文字] 變更為 [日期]。

  1. 選取 QuoteDate 資料行標頭中的 ABC 圖示,指出數據行是 Text 數據類型。

  2. 從資料類型選單選項中選擇 [日期] 選項。

    已選取 QuoteDate 資料格式圖示的螢幕快照。

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

在這項工作中,您會將 Customers 和 Quotes 數據表載入至數據模型。

  1. 從 [ 首頁 ] 功能表中,選取 [ 關閉 & 載入 > 關閉 & 載入至...]。

    已選取 [Close & Load] (關閉並載入) 按鈕,並醒目提示 [Close & Load To] (關閉並載入至) 選項的螢幕擷取畫面。

  2. 在 [匯入] 視窗中,選取 [只建立連線] 選項按鈕。

    [匯入資料] 視窗的螢幕擷取畫面,其中顯示 [只建立連線] 按鈕,並已選取 [將這個資料加入資料模型] 核取方塊。

  3. 核取 [將此數據新增至數據模型] 旁的方塊。

  4. 選取 [確定] 按鈕。

    注意

    載入的數據表會顯示在 [查詢 & 連接窗格] 視窗中,並載入數據列總數。

    [Queries & Connections] (查詢與連線) 窗格的螢幕擷取畫面,其中顯示已載入 7,560 個資料列的 Customers 查詢,以及載入 84,307 個資料列的 Quotes 查詢。

    注意

    此時,我們已使用 Power Query 連線到數據源,並選取複選框選項以將此數據新增至數據模型。 不過,我們實際上並未看到此數據已載入的位置。 在實驗室 02A 中,我們將使用 Power BI Desktop 匯入 Power Query 連線、客戶數據表和報價數據表來建立數據模型。

    注意

    若要編輯 [查詢] 並重新啟動 [Power Query 編輯器] 視窗,請選取 [數據>查詢 & 連線] 以顯示 [查詢 & 連線] 窗格。

    [資料] 索引標籤下 [Queries & Connections] (查詢與連線) 按鈕的螢幕擷取畫面。

    注意

    當您選取 [關閉並載入至... ] 並第一次設定 [匯入數據] 選項時,這是預設設定。 您一律可以編輯這些設定,方法是以滑鼠右鍵按兩下 [查詢 & 連線] 窗格中的 [ 查詢 ],然後選取 [ 載入至... ] 選項以顯示和更新 [匯入數據] 設定。

    內容對話方塊中 [載入至...] 選項的螢幕擷取畫面,其針對 [Queries & Connections] (查詢與連線) 窗格中的項目。

工作 4:儲存檔案

在此工作中,您將使用 CustomersQuotes 查詢連線來儲存 Excel 檔案。

  1. 從主要 Excel 功能區中,選取 [ 檔案 > 儲存]。

  2. 移至 C:\ANALYST-LABS\Lab 01 資料夾,然後將檔案儲存為 Lab 01 - My Solution.xlsx

摘要

在此實驗室中,您已使用 Excel 中的 Power Query 來連線至 CSV 和 XLSX 源數據檔、使用來自範例的數據行建立新的數據行、在 Power Query 中取消樞紐和套用的轉換、將源數據載入至數據模型,並使用數據連線儲存 Excel 檔案。

顯示 Customers 和 Quotes 查詢之 Excel 最終結果的螢幕擷取畫面。