您可能相當熟悉參數查詢在 SQL 或 Microsoft Query 中的使用方式。 不過Power Query參數有主要差異:
-
參數可用於任何查詢步驟。 除了做為資料篩選功能之外,參數還可以用來指定檔路徑或伺服器名稱等專案。
-
參數不會提示輸入。 您可以改用Power Query快速變更其值。 您甚至可以儲存和擷取 Excel 儲存格中的值。
-
參數會儲存在簡單的參數查詢中,但與使用的資料查詢是分開的。 建立之後,您可以視需要將參數新增至查詢。
附註 如果您想要用另一種方式建立參數查詢,請參閱在 Microsoft Query 中建立參數查詢。
您可以使用參數來自動變更查詢中的值,避免每次都編輯查詢來變更值。 您只要變更參數值即可。 建立參數後,參數會儲存在特殊參數查詢中,方便您直接從 Excel 進行變更。
-
選> [啟動Power Query 編輯器] > [取得資料>其他來源]。
-
在Power Query 編輯器中,選取 [首頁>管理參數>新參數]。
-
在 [ 管理參數]對話方塊中,選取 [ 新增]。
-
視需要設定下列專案:
姓名
這應該會反映參數的函數,但請盡可能保持簡短。
描述
這可以包含任何可協助使用者正確使用參數的詳細資料。
必要
執行下列其中一項:
任何值您可以在參數查詢中輸入任何資料類型的任何值。 值清單 您可以在小格線中輸入值,將值限制在特定清單中。 您也必須選取下方的 預設值 和 目前值 。 查詢 選取清單查詢,其類似以逗號分隔並以大括弧括住的 清單 結構化欄。 例如,[問題] 狀態欄位可能會有三個值:{「New」, 「Ongoing」, 「Closed」}。 您必須事先建立清單查詢,方法是開啟進階編輯器 (選取 [常用]>進階編輯器) 、移除程式碼範本、輸入查詢清單格式的值清單,然後選取 [完成]。 當您完成建立參數後,清單查詢會顯示在參數值中。類型
這會指定參數的資料類型。
建議的值
如有需要,請新增值清單或指定查詢以提供輸入建議。
預設值
只有在 [建議的 值 ] 設為 [ 值清單],並指定哪個清單專案為預設值時,才會出現此設定。 在此情況下,您必須選擇預設值。
目前值
視您使用參數的位置而定,如果這是空白,查詢可能不會傳回任何結果。 如果已選取 [必要 ], 則 [目前值 ] 不能是空值。
-
若要建立參數,請選取[確定]。
以下是管理資料來源位置變更並防止重新整理錯誤的方法。 例如,假設架構和資料來源相似,請建立參數以輕鬆變更資料來源,並協助避免資料重新整理錯誤。 伺服器、資料庫、資料夾、檔案名或位置有時會變更。 也許資料庫管理員偶爾會調換伺服器、每月一下 CSV 檔案會進入不同的資料夾,或者您需要在開發/測試/生產環境之間輕鬆切換。
步驟 1:建立參數查詢
在下列範例中,您有數個使用匯入資料夾作業匯入的 CSV 檔案 (選取資料>從[檔案] 取得資料>>從資料夾) 取資料夾 C:\DataFilesCSV1。 但有時會使用不同的資料夾做為放置檔案的位置,例如 C:\DataFilesCSV2。 您可以使用查詢中的參數做為不同資料夾的替代值。
-
選取 [首頁>新參數>管理參數]。
-
在 [ 管理參數 ] 對話方塊中輸入下列資訊:
姓名
CSVFileDrop
描述
替代檔案下拉位置
必要
是
類型
文字
建議的值
任何值
目前值
C:\DataFilesCSV1
-
選取 [確定]。
步驟 2:將參數新增至資料查詢
-
若要將資料夾名稱設定為參數,請在 [ 查詢設定] 的[ 查詢步驟] 底下,選取 [ 來源],然後選取 [編輯設定]。
-
請確定 [檔案路徑] 選項已設定為 [參數],然後從下拉式清單中選取您剛建立的參數。
-
選取 [確定]。
步驟 3:更新參數值
資料夾位置剛剛變更,所以現在您可以直接更新參數查詢。
-
選取 [資料 > 連線&查詢] > [ 查詢] 索引標籤,以滑鼠右鍵按一下參數查詢,然後選取 [ 編輯]。
-
在 [ 目前值] 方 塊中輸入新的位置,例如 C:\DataFilesCSV2。
-
選 取 [首頁 > 關閉&載入]。
-
若要確認結果,請將新資料新增至資料來源,然後使用更新的參數重新整理資料查詢 (選取 資料 > 重新整理所有) 。
有時候,您會想要一種簡單的方法來變更查詢的篩選,以取得不同的結果,而不需要編輯查詢或對相同查詢進行稍微不同的複本。 在此範例中,我們變更日期以方便地變更資料篩選。
-
若要開啟查詢,請找出先前從Power Query 編輯器載入的查詢,選取資料中的儲存格,然後選取[查詢>編輯]。 如需詳細資訊,請參閱 在 Excel 中建立、載入或編輯查詢。
-
選取任何欄標題中的篩選箭號來篩選資料,然後選取篩選命令,例如 [日期/時間篩選 ]> 之後]。 [篩選列] 對話方塊隨即出現。
-
選取 [ 值 ] 方塊左側的按鈕,然後執行下列其中一項操作:
-
若要使用現有的參數,請選取 [ 參數],然後從右側顯示的清單中選取您要的參數。
-
若要使用新的參數,請選取 [新增參數],然後建立參數。
-
-
在 [ 目前值 ] 方塊中輸入新日期,然後選取 [ 首頁 ]> [關閉] & [載入]。
-
若要確認結果,請將新資料新增至資料來源,然後使用更新的參數重新整理資料查詢 (選取 資料 > 重新整理所有) 。 例如,將篩選值變更為不同的日期以查看新結果。
-
在 [ 目前值] 方 塊中輸入新日期。
-
選 取 [首頁 > 關閉&載入]。
-
若要確認結果,請將新資料新增至資料來源,然後使用更新的參數重新整理資料查詢 (選取 資料 > 重新整理所有) 。
在此範例中,查詢參數中的值是從活頁簿中的儲存格讀取。 您不需要變更參數查詢,只要更新儲存格值即可。 例如,您想要依第一個字母篩選欄,但輕鬆地將值變更為從 A 到 Z 的任何字母。
-
在載入您要篩選查詢的活頁簿工作表上,建立一個包含兩個儲存格的 Excel 表格:標題和值。
MyFilter
G
-
選取 Excel 表格中的儲存格,然後選取[資料>從表格/範圍>取得資料]。隨即出現Power Query 編輯器。
-
在右側 [查詢設定] 窗格的 [名稱] 方塊中,將查詢名稱變更為更有意義的名稱,例如 FilterCellValue。
-
若要傳遞資料表中的值,而非資料表本身,請以滑鼠右鍵按一下 [資料預覽] 中的值,然後選取 [ 向下切入]。
請注意,公式已變更為 = #"Changed Type"{0}[MyFilter]
當您在步驟 10 中使用 Excel 表格做為篩選時,Power Query參照表格值做為篩選準則。 直接參照 Excel 表格會造成錯誤。
-
選 取 [首頁 > 關閉&載入] > [關閉] & [載入至]。 您現在有一個名為「FilterCellValue」的查詢參數,可在步驟 12 中使用。
-
在 [ 匯入資料 ] 對話方塊中,選取 [只建立連線],然後選取 [ 確定]。
-
開啟您要使用 FilterCellValue 資料表中的值進行篩選的查詢,該資料表先前是從Power Query 編輯器載入的值,方法是選取資料中的儲存格,然後選取[查詢>編輯]。 如需詳細資訊,請參閱 在 Excel 中建立、載入或編輯查詢。
-
選取任何欄標題中的篩選箭號來篩選資料,然後選取篩選命令,例如 [文字篩選 ]> [開始]。 [篩選列] 對話方塊隨即出現。
-
在 [值 ] 方塊中輸入任何值,例如 「G」,然後選取 [ 確定]。 在此情況下,該值是您在下一個步驟中輸入之 FilterCellValue 資料表中值的暫時預留位置。
-
選取資料編輯列右側的箭號以顯示整個公式。 以下是公式中篩選準則的範例:
= Table.SelectRows (#「Changed Type」, each Text.startsWith ([Name], 「G」) ) -
選取篩選的值。 在公式中,選取 「G」。
-
使用 M Intellisense,輸入您所建立之 FilterCellValue 表格的前幾個字母,然後從出現的清單中選取它。
-
選 取 [首頁 > 關閉 ] > [關閉] & [載入]。
結果
您的查詢現在會使用您建立的 Excel 資料表中的值來篩選查詢結果。 若要使用新值,請在步驟 1 中編輯原始 Excel 表格中的儲存格內容、將 「G」 變更為 「V」,然後重新整理查詢。
您可以控制是否允許或不允許參數查詢。
-
在 [Power Query 編輯器] 中,選取 [檔案>選項] 和 [設定] > [查詢選項] > Power Query 編輯器。
-
在左側窗格的 [全局] 底下,選取 [Power Query 編輯器]。
-
在右側窗格中的 [ 參數] 底下,選取或清除 [ 一律允許資料來源和轉換對話方塊中的參數化]。