使用 Power Query 編輯器來清理和轉換資料

已完成

我們已經使用 Microsoft Power BI Desktop 連線至資料來源,現在必須調整資料以滿足我們的需求。 有時候調整表示「轉換」資料,例如重新命名資料行或資料表、將文字變更為數值、移除資料列,或是將第一個資料列設定為標題。

Power BI Desktop 中的 Power Query 編輯器 除了可透過功能區執行工作,也可充分運用捷徑功能表 (也稱為滑鼠右鍵按一下或快顯功能表)。 在功能區上的 [轉換] 索引標籤中供您選取的大部分項目,也能在項目 (例如資料行) 上以滑鼠右鍵按一下,然後在顯示的快顯功能表上選取命令來使用。

塑形資料

當您在 Power Query 編輯器 中替資料「塑形」 時,系統會在於 Power Query 編輯器 載入及呈現資料時,向您提供其將進行以調整資料的逐步指示。 原始資料來源不受影響。 只有此特定的資料檢視才會經過調整 (或「塑形」)。

Power Query 編輯器 會記錄您指定的步驟 (例如重新命名資料表、轉換資料類型或刪除資料行)。 每當查詢連線到資料來源時,就會接著執行那些步驟,因此資料就一律會以您指定的方式塑形。 每當您在 Power BI Desktop 中使用查詢,或有任何人使用您的共用查詢 (例如,在 Power BI 服務中使用) 時,就會發生此程序。 在 [Power Query 設定] 窗格中的 [套用的步驟] 下方,會依序擷取步驟。

下圖顯示已經過塑形之查詢的 [查詢設定] 窗格。 在後續的幾個段落,我們將會逐步探討每個步驟。

已成形的查詢之查詢設定窗格的螢幕擷取畫面。

回到我們連線到 Web 資料來源找到的退休資料,開始依我們的需求為資料塑形。

注意

如果您尚未下載範例資料集,請參閱上一個單元頁面。

我們需要的資料是數字形式。 它們在此案例中沒問題,但若您必須變更資料類型,只要以滑鼠右鍵按一下欄標題,然後選 [變更類型] > [整數]。 如果您必須變更多個資料行,請選取其中一個,然後按住 Shift 鍵,同時選取其他相鄰的資料行。 接著,以滑鼠右鍵按一下資料行標題,以變更所有選取的資料行。 您也可以使用 Ctrl 鍵來選取非相鄰的資料行。

變更類型整數設定的螢幕擷取畫面。

注意

Power Query 通常會偵測到應該是數字的文字資料行,並在將資料表帶入 Power Query 編輯器時自動變更資料類型。 在此案例中,[套用的步驟] 底下的步驟會指出 Power Query 為您執行了哪些動作。

您也可以使用功能區上的 [轉換] 索引標籤,將那些資料行從文字變更 (或「轉換」) 為標題。 下圖顯示 [轉換] 索引標籤。箭號指向的 [資料類型] 按鈕,可讓您將目前資料類型轉換成其他類型。

[轉換] 功能區和 [資料類型] 功能表按鈕的螢幕擷取畫面。

請注意,[查詢設定] 窗格中列出的 [套用的步驟] 會反映已進行的所有變更。 若要從塑形程序中移除任何步驟,只需選取該步驟,然後選取左邊的 [X]。

在 [查詢設定] 窗格中套用的步驟螢幕擷取畫面,其中包含 X 以移除步驟。

連線到資料

有關不同州的資料很有趣,而且很適合用來建立額外的分析工作和查詢。 但是有一個問題:大多數資料使用兩個字母的縮寫州碼,而不是州的完整名稱。 因此,我們需要某種方式來建立州名與其縮寫的關聯。

幸運的是,有另一個公用資料來源可以做到這點,但這個方法需要經過相當程度的調整,才能在我們的退休資料表中使用。 以下是州名縮寫的 Web 資源:

http://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations

Power Query 編輯器 中,在功能區 [常用] 索引標籤上選取 [新增來源] > [Web]。 接著,輸入位址並選取 [確定]。 [導覽器] 視窗會顯示它在網頁上找到的內容。

顯示結果的 [導覽器] 視窗螢幕擷取畫面。

選取 [代碼和縮寫] 資料表,因為它包含我們想要的資料,但它需要進行相當程度的塑形來削減資料。

選取 [載入] 以將資料帶入 Power Query 編輯器,讓我們可以進行塑形。 接著,依照下列步驟執行:

  • 移除前三個資料列:那些資料列是建立網頁資料表時的產物,我們不需要它們。 若要移除它們,請在功能區的 [常用] 索引標籤上,選取 [移除資料列] > [移除前幾個資料列]。 在顯示的對話方塊中,輸入 3 做為要移除的資料列數目。

    [移除頂端資料列] 功能的螢幕擷取畫面。

  • 移除最後 26 個資料列:那些資料列全都是領土,我們不需要這些。 程序相同,但這次我們選取 [移除資料列]> [移除後幾個資料列],並輸入 26 做為要移除的資料列數目。

    [移除底端資料列] 功能的螢幕擷取畫面。

  • 篩選掉華盛頓特區:退休統計資料表不包含華盛頓特區,因此我們從清單中排除它。 做法是,選取 [region2 的名稱和狀態] 資料行旁邊的下拉式箭號,然後清除 [聯邦特區] 核取方塊。

    移除具有特定值之資料列的螢幕擷取畫面範例。

  • 移除一些不必要的資料行:我們只需要將州和州的官方兩字母縮寫進行對應,且該資訊在第二和第五個資料行已經提供。 因此,我們只需要保留那兩個資料行,並將所有其他資料行移除。 請選取要刪除的第一個資料行,然後按住 Ctrl 鍵,同時選取其他要移除的資料行 (這可讓您選取多個非連續的資料行)。 從功能區上的 [常用] 索引標籤,選取 [移除資料行] > [移除資料行]。

    移除資料行功能以移除不必要的資料行的螢幕擷取畫面。

  • 使用第一個資料列作為標題:因為我們移除了前三個資料列,目前的頂端資料列是我們所要的標題。 選取 [使用第一個資料列作為標題] 按鈕。

    [使用第一個資料列做為標頭] 功能的螢幕擷取畫面。

    注意

    這是指出 Power Query 編輯器 中套用的步驟「順序」很重要、而且可能會影響資料塑形的好時機。 務必也要考慮到某一個步驟可能會影響另一個後續步驟。 如果您將某個步驟從 [套用的步驟] 清單移除,後續步驟的行為可能因為步驟的查詢順序受到影響,不會和原本預期的一樣。

  • 重新命名資料行和資料表本身:一般會有幾種方式可以重新命名資料行。 您可以使用任何您慣用的方式。 讓我們將它們重新命名為「州名」和「州碼」。 若要重新命名資料表,只要在 [查詢設定] 窗格的 [名稱] 欄位中輸入名稱即可。 讓我們將這個資料表稱為 StateCodes

    [重新命名資料行] 功能的螢幕擷取畫面。

合併資料

現在,既然 StateCodes 資料表已經塑形,我們就可以將兩個資料表合併成一個。 因為我們現在擁有的資料表是套用至資料的查詢結果,它們通常稱為「查詢」。

有兩種主要的方式可合併查詢:「合併」和「附加」。

當您要將一或多個資料行加入另一個查詢時,您可以 合併 查詢。 當您要將額外的資料列加入現有查詢時,您可以 附加 查詢。

在此案例中,我們要合併查詢。 若要開始,請選取要「合併至」其他查詢的查詢。 接著,在功能區上的 [常用] 索引標籤上,選取 [合併查詢]。 我們要先選取退休查詢。 找到該查詢之後,將它重新命名為 RetirementStats

[合併查詢] 下拉式功能表的螢幕擷取畫面。

[合併] 對話方塊隨即顯示,提示我們選取資料表來合併到選取的資料表,以及要用來合併的相符資料行。

選取 RetirementStats 資料表 (查詢) 的 [州],然後選取 [StateCodes] 查詢 (在此案例中,選擇很簡單,因為只有一個其他查詢。 但當您連線到許多資料來源時,必須從許多查詢中選擇)。在您選取正確符合的資料行後 (RetirementStats 的 [州] 和 StateCodes 的 [州名]),[合併] 對話方塊看起來會像這樣,且 [確定] 按鈕將變為可選取。

[合併] 對話方塊的螢幕擷取畫面,其中包含 [確定] 按鈕。

在查詢結尾會建立 NewColumn,其內容為資料表 (查詢) 與現有查詢合併之後的內容。 來自被合併查詢的所有資料行都壓縮在 NewColumn,但您可以 展開 資料表,並包含您想要的任何資料行。 若要展開合併的資料表,並選取要包含的資料行,請選取展開圖示 (展開圖示)。 [展開] 對話方塊隨即顯示。

可用展開對話方塊的螢幕擷取畫面。

在此案例中,我們只想要包含 [州碼] 資料行。 因此,只選取該資料行,然後選取 [確定]。 您也可以清除 [使用原始資料行名稱作為前置詞] 核取方塊。 如果您讓它維持選取狀態,合併的資料行將會命名為 [NewColumn.州碼] (原始的資料行名稱,或是 NewColumn 然後一個點,然後是被帶入查詢的資料行名稱)。

注意

如有需要,您可以試看看 [NewColumn] 資料表帶入查詢的方式。 如果您不喜歡結果,只需從 [查詢設定] 窗格中的 [套用的步驟] 清單中刪除 [展開] 步驟即可。 您的查詢將會還原為您在套用該步驟之前的狀態。 這就像免費的加工,您要做幾次都可以,直到展開程序看起來是您想要的方式。

我們現在有了合併兩個資料來源的單一查詢 (資料表),而這兩個資料來源各自都已塑形成與我們需求相符的形式。 此查詢可以作為許多其他有趣資料連線的基礎,例如居住成本統計資料、人口統計資料,或任何一州的工作機會。

若要在 Power Query 編輯器 中套用變更並將其載入 Power BI Desktop,請選取功能區上 [常用] 索引標籤中的 [關閉並套用]。

[首頁] 索引標籤上 [關閉] 和 [套用] 功能表的螢幕擷取畫面。

模型中的資料現在已經可供使用。 接下來,我們將為您的報表建立一些視覺效果。

我們現在有足夠的資料,可以在 Power BI Desktop 中建立一些有趣的報表。 因為這是一個里程碑,讓我們儲存這個 Power BI Desktop 檔案。 在功能區的 [常用] 索引標籤上,選取 [檔案] > [儲存] 以儲存報表,我們將它稱為 開始使用 Power BI Desktop

太棒了! 現在請繼續進行下一個單元,我們將建立一些有趣的視覺效果。