在 Power Query (建立)

您可能相當熟悉參數查詢在 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)

需要更多協助?

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×