Power Query 提供數種將 Power Query 建立和載入至您活頁簿中的方式。 您也可以在 [查詢選項]視窗中設定預設查詢載入設定。
祕訣 若要判斷工作表中的資料是否由 Power Query 塑造,請選取資料儲存格,而如果 [Query]內容功能區索引標籤隨即出現,則資料是從 Power Query 載入。
知道您目前身處的環境 Power Query 已完全與 Excel 使用者介面整合,尤其是當您匯出資料、使用連線,以及編輯 [樞紐分析表]、Excel 表格和命名範圍時。 為避免混淆,請隨時了解您目前位於何種環境 (Excel 或 Power Query),這點非常重要。
熟悉的 Excel 工作表、功能區與方格 |
Power Query 編輯器功能區與資料預覽 |
例如,在 Excel 工作表中處理資料基本上與 Power Query 不同。 此外,在 Excel 工作表中看到的連線資料可能或可能沒有 Power Query 在幕後作業以塑造資料。 這只有在您從 Power Query 將資料載入工作表或 [資料模型] 時才會發生。
重新命名工作表索引標籤 建議以有意義的方式重新命名工作表索引標籤,特別是如果您有很多索引標籤。 尤其重要的是要釐清資料工作表以及從 Power Query 編輯器載入之資料工作表之間的差異。 即使您只有兩個工作表,一個是 Excel 表格 (稱為 Sheet1),而另一個是匯入該 Excel 表格所建立的查詢 (稱為 Table1),這兩者很容易被混淆。 將工作表索引標籤的預設名稱變更為對您較有意義的名稱,一向是很好的做法。 例如將 Sheet1 變更為 DataTable,還有將 Table1 變更為 QueryTable。 現在就很容易分辨哪個索引標籤有資料,哪個索引標籤有查詢。
您可以從匯入的資料建立查詢,或建立空白查詢。
從匯入的資料建立查詢
這是建立查詢最常見的方法。
-
匯入一些資料。 如需詳細資訊,請參閱從外部資料來源匯入資料。
-
選取資料中的儲存格,然後選取 [查詢]> [編輯]。
建立空白查詢
建議您從頭開始。 有兩種方法可以執行此操作。
-
選取 [資料]> [取得資料]> [從其他來源]> [空白查詢]。
-
選取 [資料]> [取得資料]> [啟動 Power Query 編輯器]。
如果您非常熟悉 Power Query M 公式語言,可以在此時手動新增步驟與公式。
或者您可以選取 [首頁],然後在 [新查詢]群組中選取命令。 執行下列其中一項操作。
-
選取 [新來源] 以新增資料來源。 這個命令就像在 Excel 功能區中的 [資料]> [取得資料] 命令一樣。
-
選取 [最近來源]以從您一直在使用的資料來源中選取。 這個命令就像在 Excel 功能區中的 [資料]> [最近來源] 命令一樣。
-
選取 [輸入資料]以手動輸入資料。 您可以選擇這個命令,在沒有外部資料來源的情況下試用 Power Query 編輯器。
假設您的查詢有效且沒有錯誤,您可以將它載入回工作表或 [資料模型]。
從 Power Query 編輯器載入查詢
在 Power Query 編輯器中,執行下列其中一項操作:
-
若要載入至工作表,請選取 [首頁]> [關閉並載入]> [關閉並載入]。
-
若要載入至 [資料模型],請選取 [首頁]> [關閉並載入]> [關閉並載入至]。
在 [匯入資料]對話方塊中,選取 [新增此資料至資料模型]。
祕訣 有時 [載入]命令會變暗或停用。 這在您第一次建立活頁簿中的查詢時很可能會發生。 如果發生這種情況,請選取 [關閉並載入],在新的工作表中選取 [資料]> [查詢與連線]> [查詢]索引標籤,以滑鼠右鍵按一下查詢,然後選取 [載入至]。 或者可在 Power Query 編輯器功能區上選取 [查詢]> [載入至]。
從 [查詢與連線] 窗格載入查詢
在 Excel 中,您可能會想要將查詢載入到另一個工作表或 [資料模型]。
-
在 Excel 中,選取 [資料]> [查詢與連線],然後選取 [查詢]索引標籤。
-
在查詢清單中找出該查詢,以滑鼠右鍵按一下查詢,然後選取 [載入至]。 [匯入資料]對話方塊隨即出現。
-
決定要匯入資料的方式,然後選取 [確定]。 如需使用此對話方塊的相關資訊,請選取問號 (?)。
有幾種將查詢編輯載入至工作表的方式。
編輯從 Excel 工作表中資料的查詢
-
若要編輯查詢,請找出之前從 Power Query 編輯器載入的查詢,選取資料中的儲存格,然後選取 [查詢]> [編輯]。
編輯來自 [查詢與連線] 窗格的查詢
當您在一個同一個活頁簿中有許多查詢且想要快速尋找某個查詢時,[查詢與連線]窗格會比較好用。
-
在 Excel 中,選取 [資料]> [查詢與連線],然後選取 [查詢]索引標籤。
-
在查詢清單中找出該查詢,以滑鼠右鍵按一下查詢,然後選取 [編輯]。
編輯來自 [查詢屬性] 對話方塊的查詢
-
在 Excel 中,選取 [資料]> [資料與連線]>[查詢]索引標籤,以滑鼠右鍵按一下查詢,然後分別選取 [屬性],[屬性]對話方塊中的 [定義]索引標籤,然後選取 [編輯查詢]。
提示 如果您在具有查詢的工作表中,請選取 [資料]> [屬性],選取 [屬性]對話方塊中的 [定義]索引標籤,然後選取 [編輯查詢]。
[資料模型] 通常包含數個以關聯性排列的表格。 您可使用 Load To 命令來顯示 [匯入資料]對話方塊,然後選取 [將此資料新增至資料模式]核取方塊,以將查詢載入至 [資料模型]。 如需有關資料模型的詳細資訊,請參閱找出活頁簿資料模型中已使用哪些資料來源、在 Excel 中建立資料模型,以及使用多個資料表建立樞紐分析表。
-
若要開啟 [資料模型],請選取 [Power Pivot]> [管理]。
-
在 Power Pivot 視窗底部,選取您想要表格的工作表索引標籤。
請確認正確的表格有隨即出現。 [資料模型] 可包含許多表格。 -
請注意表格的名稱。
-
若要關閉 Power Pivot 視窗,請選取 [檔案]> [關閉]。 取回記憶體可能需要數秒鐘。
-
選取 [資料]> [連線與屬性]> [查詢]索引標籤,以滑鼠右鍵按一下查詢,然後選取 [編輯]。
-
在 Power Query 編輯器中完成變更後,請選取 [檔案]> [關閉並載入]。
結果
更新工作表中的查詢和 [資料模型] 中的表格。
如果您發現將查詢載入至 [資料模型] 所花的時間比載入至工作表的時間長很多,請檢查您的 Power Query 步驟,看看您是否使用 Contains 運算子篩選文字欄或清單結構化欄位。 此動作會導致 Excel 再次列舉每一列的整個資料集。 此外,Excel 會無法有效地使用多執行緒執行。 請嘗試使用不同的運算子 (例如 Equals 或 Begins With) 以做為解決方法,。
Microsoft 已知曉此問題,正在調查中。
您可以載入 Power Query:
-
至工作表。 在 Power Query 編輯器中,選取 [首頁]> [關閉並載入]> [關閉並載入]。
-
至資料模型。 在 Power Query 編輯器中,選取 [首頁]> [關閉並載入]> [關閉並載入至]。
根據預設,Power Query 在載入單一查詢時會將查詢載入至新的工作表,而會將多個查詢同時載入至 [資料模型]。 您可以變更所有活頁簿的預設行為,或只變更目前的活頁簿。 設定這些選項時,Power Query 不會變更工作表中的查詢結果,或 [資料模型] 資料與註釋。 您也可以使用在選取 [關閉與載入至]之後隨即出現的 [匯入]對話方塊,以動態覆寫查詢的預設設定。
適用於所有活頁簿的通用設定
-
在 PowerQuery 編輯器中,選取 [檔案]> [選項與設定]> [查詢選項]。
-
在 [查詢選項]對話方塊中,於左側 [全域] 區段下選取 [資料載入]。
-
在 [預設查詢載入設定]區段下,執行下列操作:
-
選取 [使用標準載入設定]。
-
選取 [指定自訂預設載入設定]、 然後選取或清除 [載入至工作表]或 [載入至資料模型]。
-
提示 在對話方塊底部,您可以選取 [還原預設值]以方便回到預設設定。
只適用於目前活頁簿的活頁簿設定
-
在[查詢選項]對話方塊中,於左側 [目前活頁簿]區段下選取 [資料載入]。
-
執行下列其中一或多個動作:
-
在 [類型偵測]下,選取或清除 [偵測非結構化來源的欄類型和標頭]。
預設行為是偵測它們。 如果您想要自己塑造資料,請清除此選項。 -
在 [關聯性]下,選取或清除 [第一次新增到資料模型時建立表格之間的關聯性]。
在載入至 [資料模型] 前,預設行為是尋找表格之間的現有關聯 (例如關聯式資料庫中的外部索引鍵),然後將它們以資料匯入。 如果您想要自己操作,請清除此選項。
-
在 [關聯性]下,選取或清除 [重新處理載入至資料模型的查詢時,更新關聯性]。
預設行為是不要更新關聯性。 當重新處理已載入至資料模型的查詢時,Power Query 會尋找表格之間的現有關聯 (例如關聯式資料庫中的外部索引鍵),然後將它們更新。 這可能會移除在資料輸入後手動建立的關聯性,或介紹新關聯性。 不過,如果您想要如此操作,請選取此選項。 -
在 [背景資料]下,選取或清除 [允許資料預覽在背景中下載]。
預設行為是在背景中下載資料預覽。 如果您想要馬上看到所有資料,請清除此選項。
-