閱讀英文

共用方式為


錯誤處理

與 Excel 和 DAX 語言如何具有函 IFERROR 式類似,Power Query 有自己的語法來測試和攔截錯誤。

如在 Power Query 中處理錯誤一文中所述,錯誤可能會出現在步驟或數據格層級。 本文著重於如何根據您自己的特定邏輯來攔截和管理錯誤。

注意

為了示範此概念,本文會使用 Excel 活頁簿作為其數據源。 此處所展示的概念適用於Power Query 中的所有值,而不只是來自Excel活頁簿的概念。

此示範的範例數據源是具有下表的 Excel 活頁簿。

Excel 活頁簿中範例數據的螢幕快照,其中包含三個錯誤。

Excel 活頁簿中的這個資料表有 Excel 錯誤,例如標準速率數據行中的 #NULL!、#REF!#DIV/0! 。 當您將此數據表匯入 Power Query 編輯器時,下圖顯示其外觀。

Power Query 中載入之範例數據表的螢幕快照。

請注意 Excel 活頁簿中的錯誤如何以每個儲存格中的值來顯示 [Error]

在本文中,您將瞭解如何將錯誤取代為另一個值。 此外,您也會瞭解如何攔截錯誤,並將其用於您自己的特定邏輯。

尋找錯誤時提供替代值

在此情況下,目標是在範例數據源中建立新的最終速率數據行,以使用標準速率數據行的值。 如果有任何錯誤,則會使用對應 [特殊速率 ] 資料行中的值。

若要建立新的自定義數據行,請移至 [ 新增數據行 ] 功能表,然後選取 [ 自定義數據行]。 在 [ 自訂資料列 ] 視窗中,輸入公式 try [Standard Rate] otherwise [Special Rate]。 將此新數據行 命名為 Final Rate

開啟 [自定義數據行] 對話框的螢幕快照,並嘗試在自定義數據行中輸入公式。

此公式會嘗試評估標準速率數據行,並在找不到錯誤時輸出其值。 如果在 [標準速率] 資料行中找到錯誤,則輸出是在 語句之後otherwise定義的值,在此案例中為 [特殊速率] 數據行。

將正確的數據類型新增至數據表中的所有數據行之後,下圖顯示最終數據表的外觀。

數據表的螢幕快照,其中包含最終速率數據行中特殊速率所取代的標準速率錯誤。

注意

作為替代方法,您也可以輸入公式 ,這相當於上一個公式 try [Standard Rate] catch ()=> [Special Rate],但使用 catch 關鍵詞搭配不需要參數的函式。

關鍵詞 catch 於 2022 年 5 月引進 Power Query。

提供您自己的條件式錯誤邏輯

使用與上一節相同的範例數據源,新的目標是建立 最終速率的新數據行。 如果標準速率中的值存在,則會使用該值。 否則會使用 [特殊速率] 數據行中的值,但有任何#REF!錯誤的數據列除外。

注意

排除 #REF! 錯誤的唯一目的是為了示範目的。 透過本文所展示的概念,您可以從錯誤記錄鎖定您選擇的任何字段。

當您選取錯誤值旁的任何空格符時,您會在畫面底部取得詳細資料窗格。 詳細資料窗格同時包含錯誤原因和DataFormat.Error錯誤訊息: Invalid cell value '#REF!'

所選錯誤的螢幕快照,對話框底部有錯誤訊息。

您一次只能選取一個數據格,因此您一次只能檢查一個錯誤值的錯誤元件。 此時您會建立新的自定義數據行,並使用 try 表示式。

搭配自訂邏輯使用try

若要建立新的自定義數據行,請移至 [ 新增數據行 ] 功能表,然後選取 [ 自定義數據行]。 在 [ 自訂資料列 ] 視窗中,輸入公式 try [Standard Rate]。 將此新數據行 命名為 [所有錯誤]。

螢幕快照:開啟 [自定義數據行] 對話框,並嘗試在自定義數據行中輸入公式。

表達式 try 會將值和錯誤轉換成記錄值,指出表達式是否 try 處理錯誤,以及適當的值或錯誤記錄。

數據表的螢幕快照,其中已新增 [所有錯誤] 資料行,以及顯示為 [記錄] 的錯誤。

您可以使用記錄值展開這個新建立的數據行,並選取數據行標頭旁的圖示,查看要展開的可用字段。

[所有錯誤] 數據行的螢幕快照,其中已強調展開圖示,並已選取 [HasError]、[值] 和 [錯誤] 方塊。

此作業會公開三個新的欄位:

  • 所有 Errors.HasError — 顯示標準速率數據行的值是否有錯誤。
  • 所有 Errors.Value—如果標準速率數據行的值沒有錯誤,則此數據行會顯示標準速率數據行的值。 對於發生錯誤的值,此字段無法使用,而且展開作業期間,此數據行具有 null 值。
  • 所有 Errors.Error—如果標準速率數據行的值發生錯誤,則此數據行會顯示標準速率數據行中值的錯誤記錄。 對於沒有錯誤的值,此字段無法使用,而且展開作業期間,此數據行具有 null 值。

具有數據行中新欄位的數據表螢幕快照,其中已選取一個 All.Errors.Error 值,並顯示資料表下方的錯誤訊息。

若要進一步調查,您可以展開 [ 所有 Errors.Error ] 數據行,以取得錯誤記錄的三個元件:

  • 錯誤原因
  • 錯誤訊息
  • 錯誤詳細數據

執行展開作業之後,[ 所有 Errors.Error.Message ] 欄位會顯示特定的錯誤訊息,告訴您每個單元格的 Excel 錯誤。 錯誤訊息衍生自 錯誤記錄的 [錯誤訊息 ] 欄位。

顯示特定錯誤訊息的螢幕快照。

現在,有了新數據行中的每個錯誤訊息,您就可以建立名稱為 Final Rate 和下列子句的新條件數據行:

  • 如果 [所有 Errors.Errors.Message] 數據行中的值等於 null,則輸出會是 [標準速率] 資料行的值。
  • 否則,如果 [所有 Errors.Errors.Message] 數據行中的值不相等Invalid cell value '#REF!'.,則輸出會是 [特殊速率] 資料行的值
  • 否則為 null。

[新增條件數據行] 對話框的螢幕快照,其中已設定新數據行的所有錯誤條件。

在只保留 [帳戶]、[標準費率][特殊費率] 和 [最終費率] 數據行,併為每個數據行新增正確的數據類型之後,下圖會示範最終數據表的外觀。

具有適當數據類型之其餘數據行之最終數據表的螢幕快照。

使用 trycatch 搭配自定義邏輯

或者,您也可以使用 trycatch 關鍵詞建立新的自定義數據行。

try [Standard Rate] catch (r)=> if r[Message] <> "Invalid cell value '#REF!'." then [Special Rate] else null

自定義數據行對話框,其中包含顯示 try 和 catch 語法方法的新公式。

更多資源