您可能相當熟悉參數查詢在 SQL 或 Microsoft Query 中的使用。 不過,Power Query 參數有主要差異:    

  • 參數可用於任何查詢步驟。 除了做為資料篩選功能之外,參數還可以用來指定檔案路徑或伺服器名稱等專案。

  • 參數不會提示輸入。 您可以改為使用 Power Query 快速變更其值。 您甚至可以儲存及從儲存格中Excel。

  • 參數會儲存于簡單的參數查詢中,但與所使用的資料查詢不同。  建立之後,您可以根據需要新增參數至查詢。

注意    如果您想要以其他方式建立參數查詢,請參閱在 Microsoft Query 中建立參數查詢。

您可以使用參數來自動變更查詢中的值,並避免每次變更值時編輯查詢。 您只要變更參數值。 建立參數後,參數會儲存于特殊參數查詢中,您可以直接從Excel。

  1. 選取資料>取得資料>其他來源>啟動 Power Query 編輯器

  2. 在 Power Query 編輯器中,選取 >管理參數>新增參數

  3. 在 [ 管理參數>對話方塊中,選取 [ 新增>

  4. 根據需要設定下列專案:

    名稱    

    這應該反映參數的函數,但請盡可能保持簡短。

    描述    

    這可以包含任何可協助使用者正確使用參數的詳細資訊。

    必要    

    執行下列其中一項操作:任何值 您可以在參數查詢中輸入

    任何資料類型的任何值。

    值清單    您可以在小型格線中輸入值,將值限制為特定清單。 您也必須在下方選取預設值和目前值。

    查詢選取清單查詢,其類似以逗號分隔且以大括弧括住的清單結構化資料行。

    例如,問題狀態欄位可能具有三個值:{"New","進行中","已關閉"}。 您必須事先建立清單查詢,請開啟進位編輯器 (選取> 進位編輯器) 、移除程式碼範本、以查詢清單格式輸入值清單,然後選取完成

    建立完參數之後,清單查詢會顯示在參數值中。

    類型    

    這會指定參數的資料類型。

    建議的值    

    如果需要,請新增值清單或指定查詢來提供輸入建議。

    預設值

    只有在將 建議 的值設定為值 清單,並指定預設的清單專案時,才會出現此設定。 在這種情況下,您必須選擇預設值。

    目前值    

    根據您使用參數的地方,如果這是空白,查詢可能不會返回任何結果。 如果 選取 了必要的值, 則目前值 不能為空白。

  5. 若要建立參數,請選取確定 。

以下是管理資料來源位置變更的方法,並有助於防止重新檢查錯誤。 例如,假設架構和資料來源類似,請建立參數以輕鬆變更資料來源,並有助於防止資料重新更新錯誤。 有時候伺服器、資料庫、資料夾、檔案名或位置會變更。 也許資料庫管理員偶爾會調換伺服器、每月一個 CSV 檔案下拉到不同的資料夾,或者您需要在開發/測試/生產環境之間輕鬆切換。

步驟 1:建立參數查詢

在下列範例中,您有幾個 CSV 檔案會使用輸入資料夾作業 (選取資料>取得資料> 從檔案>從資料夾) 資料夾 C:\DataFilesCSV1。 但有時候,有時候會使用不同的資料夾做為放置檔案的位置,C:\DataFilesCSV2。 您可以使用查詢中的參數做為不同資料夾的替代值。

  1. 選取主>管理參數>新參數

  2. 在 [管理參數> 對話方塊中 輸入 下列資訊:

    名稱

    CSVFileDrop

    描述

    替代檔案下拉位置

    必要

    類型

    簡訊

    建議的值

    任何值

    目前值

    C:\DataFilesCSV1

  3. 選取 [確定]。

步驟 2:新增參數至資料查詢

  1. 若要將資料夾名稱設定為參數,請在查詢 設定中,選取在查詢步驟下的 ,選取來源,然後選取設定。

  2. 請確定將 檔案路徑 選項設定為參數,然後從下拉式清單中選取您剛剛建立的參數。

  3. 選取 [確定]。

步驟 3:更新參數值

資料夾位置剛剛變更,因此現在您可以直接更新參數查詢。

  1. 選取[>查詢&查詢>索引鍵,以滑鼠右鍵按一下參數查詢,然後選取 [編輯> 。

  2. 在目前的值方塊中輸入 新位置, 例如 C:\DataFilesCSV2

  3. 選取首頁>關閉&載入

  4. 若要確認結果,請新增資料至資料來源,然後使用更新的參數重新 (選取資料 > 全部) 。

有時候,您想要輕鬆變更查詢的篩選,以取得不同的結果,而不需要編輯查詢,或製作稍微不同的同一個查詢複本。 在此範例中,我們會變更日期,方便您變更資料篩選。

  1. 若要開啟查詢,請找出先前從 Power Query 編輯器載入的查詢,選取資料中的儲存格,然後選取查詢>編輯。 詳細資訊請參閱在 Excel中建立、載入或編輯Excel。

  2. 選取任何欄標題中的篩選箭鍵以篩選資料,然後選取篩選命令,例如日期 / 時間篩選> 之後。 [篩選列 > 對話方塊即會顯示。

    在 [篩選與篩選?

  3. 選取值方塊左側 的按鈕, 然後執行下列其中一項操作:

    • 若要使用現有的參數,請選取 參數,然後從右側顯示的清單選取您想要的參數。

    • 若要使用新參數,請選取 新參數,然後建立參數。

  4. 在目前的值方塊中輸入新日期,然後選取>關閉&載入

  5. 若要確認結果,請新增資料至資料來源,然後使用更新的參數重新 (選取資料 > 全部) 。 例如,將篩選值變更為不同的日期,以查看新結果。

  6. 在目前的值方塊 中輸入新 日期。

  7. 選取首頁>關閉&載入

  8. 若要確認結果,請新增資料至資料來源,然後使用更新的參數重新 (選取資料 > 全部) 。

在此範例中,查詢參數中的值會從您活頁簿中的儲存格讀取。 您不需要變更參數查詢,只要更新儲存格值。 例如,您想要以第一個字母篩選欄,但輕鬆地將值變更為 A 到 Z 的任何字母。

  1. 在載入要篩選之查詢的活頁簿工作表上,Excel一個包含兩個儲存格的表格:一個標題和一個值。
     

    MyFilter

    G

  2. 選取資料表中的Excel儲存格,然後選取資料>資料>從資料表/範圍取得資料。Power Query 編輯器會出現。

  3. 右側查詢設定的名稱方塊中,將查詢名稱變更為更有意義,例如 FilterCellValue。 

  4. 若要傳遞資料表中的值,而不是資料表本身,請在 [資料預覽中以滑鼠右鍵按一下值,然後選取 [向下切入> 。

    請注意,公式已變更為 = #"Changed Type"{0}[MyFilter]

    當您在步驟 10 Excel表格做為篩選時,Power Query 會參照 Table 值做為篩選準則。 直接參照資料表Excel會導致錯誤。

  5. 選取>關閉&載入>關閉&載入至。 您現在有一個名為「FilterCellValue」的查詢參數,用於步驟 12。

  6. 在 [ 輸入資料」 對話方塊中,選取 [ 僅建立連接,然後選取 [確定> 。

  7. 在 FilterCellValue 資料表中,選取資料中的儲存格,然後選取查詢>編輯,以開啟您想要篩選的查詢。 詳細資訊請參閱在 Excel中建立、載入或編輯Excel。

  8. 選取任何欄標題中的篩選箭鍵以篩選資料,然後選取篩選命令,例如文字篩選>開頭。 [篩選列 > 對話方塊即會顯示。 

  9. 在值方塊中輸入任何值,例如「G」,然後選取 「確定」。 在此案例中,該值是您于下一個步驟中輸入的 FilterCellValue 資料表中的值暫時預留位置。

  10. 選取資料編輯欄右邊的箭箭,以顯示整個公式。 以下是公式中的篩選準則範例:

    = Table.SelectRows (#"已變更類型",每個文字。[ ( name],"G") )

  11. 選取篩選的值。 在公式中,選取 「G」。

  12. 使用 M Intellisense,輸入您建立之 FilterCellValue 資料表的開頭幾個字母,然後從出現的清單中選取該字母。

  13. 選取首頁>關閉>關閉&載入

結果

您的查詢現在會使用您Excel資料表中的值來篩選查詢結果。 若要使用新值,請編輯步驟 1 中原始 Excel表格中的儲存格內容,將 "G" 變更為 "V",然後重新更新查詢。

您可以控制參數查詢是否允許。

  1. 在 Power Query 編輯器中,選取>選項設定>查詢選項>查詢編輯器

  2. 在左側窗格中的 GLOBAL下,選取 Power Query 編輯器

  3. 在右側窗格的 [參數> 下,選取或清除 [永遠允許在資料來源和轉換對話方塊中 進行參數設定> 。

另請參閱

Power Query for Excel説明

使用查詢參數 (docs.com)

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×