資料轉換實驗室

已完成

存取環境

開始此實驗室之前 (除非您從先前的實驗室繼續進行),請選取上方的 [啟動實驗室]。

您會自動以 data-ai\student 的身分登入實驗室環境。

您現在可開始使用此實驗室。

提示

若要將實驗室環境固定,使其填滿視窗,請選取頂端的 [電腦] 圖示,然後選取 [讓視窗符合電腦]。

實驗室螢幕擷取畫面,其中已選取 [電腦] 圖示並反白顯示 [讓視窗符合電腦] 選項。

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

連線至資料

  1. 開啟 Power BI Desktop。

  2. 從提示中選取 [取得資料]。

  3. 醒目提示 Excel,然後選取 [連接] 按鈕。

  4. 瀏覽至 D:\Power-BI-Tableau\Lab-02\Data

  5. 開啟 raw_TailspinToys2019-US.xlsx。

  6. 勾選下列資料表的方塊,然後選取 [載入]。

    • Dbo_Region

    • 2017_Sales

    • 2018_Sales

    • 2019_Sales

    • 產品詳細資料

    注意

    dbo_Region 和 Region 有何差異?dbo_Region 在 Excel 中是定義為資料表。 Region 則是名稱為 Region 的整個 Excel 索引標籤。

  7. 請注意,在 [欄位] 窗格下載入的資料表。

  8. 按一下最左邊的資料表,開啟 [資料預覽] 窗格。 查看您載入的每個資料表,以熟悉資料。 看看是否要進行任何變更?

    左側資料表圖示的螢幕擷取畫面。

轉換資料

  1. 選取頂端功能區中的 [轉換資料]。 隨即開啟 Power Query 編輯器。

    Power BI 工具功能區的螢幕擷取畫面,其中包含醒目提示的 [轉換資料]。

  2. 選取左側的 [產品詳細資料查詢]。 記下右側 [查詢設定] 的內容。 請注意,其中有些已套用的步驟。 根據預設,載入 Excel 檔案時,系統會寫入 M 程式碼以正確載入資料並讀取標頭。

清理和樞紐分析產品資料表:轉置和修剪

  1. 產品資料沒有資料行標頭,但有資料列標頭。 這樣很難使用。

  2. 在 [轉換] 功能區底下,選取 [轉置]。

  3. 在 [首頁] 功能區底下,選取 [使用第一個資料列作為標頭]。 現在您已完成產品的維度資料表。 重新命名資料表,並在右側的屬性下查詢 "Product"。

  4. "Product Category" 資料看起來好像不太對勁。 可以將其清除嗎?

  5. 這裡有前置空格。 您可以選取資料行、以滑鼠右鍵按一下並選取 [轉置] > [修剪資料],移除資料中的前置或後置空格。

銷售資料:附加

您可以更輕鬆地繪製時間趨勢,並在所有歷史銷售資料都在單一位置時執行各年度的計算。

  1. 按一下 [Sales 2019],移至 [首頁] 索引標籤,選取功能區右側的 [附加查詢],然後選取 [三個以上的資料表]。

  2. 在 [要新增的資料表] 下新增 [Sales 2018] 和 [Sales 2017],然後選取 [確定]。

  3. 按一下 [訂單日期] 的下拉式清單,然後按一下 [載入更多],以確定您有這三年的所有資料。 您應該會在相同資料來源中看到 2017、2018 和 2019 年的日期。

  4. 將查詢重新命名為 "Sales"。

  5. 以滑鼠右鍵按一下 [2017 Sales],然後選取 [啟用載入] 將其取消勾選。 針對 [2018 Sales] 執行相同的動作。 如果彈出警告視窗,請選取 [繼續]。 您不需要重複將 2017 和 2018 年的資料載入報表。 所有資訊都可以在新的 [Sales] 資料表中取得。

從銷售資料建立客戶維度資料表:建立重複項目、移除重複項目、重新命名資料行

[Sales] 資料表中有客戶詳細資料。 這些詳細資料通常是每個客戶都有的重複冗長文字字串資料。 這時就很適合使用維度資料表,以避免儲存重複的資訊。

  1. 按一下 CustomerStateID 資料行,並將其拖曳至 first_name 資料行左邊。

  2. 建立 [Sales] 資料表的重複項目。 將 Sales (2) 重新命名為 Customer。 在您取消勾選 [啟用載入] 的相同位置尋找建立重複項目的選項。

  3. 按住 Ctrl 鍵並選取下列資料行:CustomerStateID、first_name、last_name 和 email。

  4. 以滑鼠右鍵按一下資料行標題,然後選取 [移除其他資料行]。

  5. 在選取電子郵件資料行下方,以滑鼠右鍵按一下資料行標頭,然後選取 [移除重複項目]。

  6. 在電子郵件下拉式篩選器上,選取 [移除空白]。 這會移除不該在維度資料表中出現的空白和 null 值。

  7. 以滑鼠右鍵按一下電子郵件欄標題,然後選取 [從範例新增資料行]。

  8. 將這個新的資料行重新命名為 Company。

  9. 開始鍵入您認為與客戶電子郵件地址相關聯的公司名稱。 例如,若是 fdodgson@contoso.com,相關公司可能是 Contoso。

    [從範例新增資料行] 的螢幕擷取畫面,其中已選取 [電子郵件]。

  10. 現在您已完成一個簡單的重要客戶資訊資料表。

  11. 從 Sales 查詢中移除 "CustomerStateID"、"first_name" 和 "last_name" 資料行。 請務必保留 "email",這是我們將 [Customer] 資料表聯結至銷售資訊的唯一指標。

合併狀態和區域維度:聯結

我們有下列兩個 StateID 參考,但沒有 State 名稱。 Customer.CustomerStateID 和 Sales.OriginationStateID。

  1. 將 "state_lookup.csv" 載入查詢編輯器。

    dbo_Region 和 state_lookup 查詢會建立簡單的維度資料表。 為了簡潔起見,讓我們將這些資料表合併成一個名為 Geography 的新資料表。

  2. 選取 state_lookup 查詢,以便預覽 state_lookup 資料表。

  3. 選取頂端功能區中的 [合併查詢]。

    Power Query 編輯器功能區的螢幕擷取畫面,其中包含醒目提示的 [合併查詢]。

  4. 選取 state_lookup 資料表中的 RegionID 資料行。 將聯結加入 Region_dbo 資料表中,並選取 RegionID。 您應該會看到 [state_lookup] 資料表中顯示 51 筆記錄全數相符。

    [合併] 對話方塊的螢幕擷取畫面,其中已選取 state_lookup 和 dbo_Region,並將 [聯結種類] 設定為 [左方外部]。

  5. 聯結資料表之後,所有來自 "dbo_Region" 的欄位都在單一資料行中。

  6. 選取資料行標頭中的轉向箭號,即可只新增您想要的區域資訊。 然後,務必只選取 "RegionName"。 取消勾選取消勾選 [使用原始資料行名稱做為前置詞]。

    [區域] 右邊箭號的螢幕擷取畫面,其中顯示已選取 RegionName 及 [使用原始資料行名稱做為前置詞] 的下拉式清單。

  7. 將 state_lookup 資料表重新命名為 "Geography"。

  8. 刪除 Region ID 資料行。

  9. 由於 [Geography] 維度資料表包含我們所需的區域資訊,因此我們不需要載入 [Region] 資料表。 取消勾選 [啟用載入]。 當警告方塊彈出時,請選取 [繼續]。 我們已將此資料移轉至 Geography 查詢。 因為此資料來源是 [Geography] 資料表的輸入,所以無法將其全部刪除。

設定關聯性

  1. 點擊 [關閉並套用]。 這會關閉查詢編輯器,並將您的變更載入 Power BI Desktop 以及該檔案中的任何報告。

  2. 選取左側的「資料模型」圖示。

    左側「資料模型」圖示的螢幕擷取畫面。

  3. 重新排列資料表,使其看起來約略像下圖。

    資料表的螢幕擷取畫面,其排列方式為 [Geography] 位於左上方,[Customer] 位於左下方,[Product] 位於右上方,而中間是 [Sales] 聯結。

  4. 在下一個示範中,我們將建立這些資料表之間的關聯性。

如果您提早完成

  • 將 [Customer] 資料表連接到 [Sales] 的唯一方法是使用 "email"。 設定長字串的關聯性並沒有效率。 您是否能建立一個 Customer ID 欄位來連結兩個資料表?

總結

完成此實驗室之後,您應該有下列資料來源可用於報表。

  • 產品

  • 地理位置

  • 客戶

  • Sales

在 [資料模型] 窗格中,您應該會看到下列資料表。

資料表的螢幕擷取畫面,其排列方式為 [Geography] 位於左上方,[Customer] 位於左下方,[Product] 位於右上方,而中間是 [Sales] 聯結。

如果您有任何問題,請詢問講師。 我們會將這份 Power BI 活頁簿用於未來的實驗室和活動。

資料定義

在 Excel 中:

ProductDetails.WholesalePrice = Tailspin Toys 為製造/取得產品所支付的成本

Sales.UnitPrice = 客戶 每單位 產品的未折扣費用

Sales.DiscountAmount = 可從單價減去的 每單位 折扣

SaleAmt = 客戶支付的折扣價格,亦即 [UnitPrice] - [DiscountAmount]