Obsługa błędów

Podobnie jak program Excel i język DAX mają funkcję, dodatek Power Query ma własną IFERROR składnię do testowania i przechwytywania błędów.

Jak wspomniano w artykule dotyczącym obsługi błędów w dodatku Power Query, błędy mogą występować na poziomie kroku lub komórki. W tym artykule skupisz się na tym, jak można przechwytywać błędy i zarządzać nimi na podstawie własnej konkretnej logiki.

Uwaga

Aby zademonstrować tę koncepcję, ten artykuł będzie używać skoroszytu programu Excel jako źródła danych. Zaprezentowane tutaj koncepcje dotyczą wszystkich wartości w dodatku Power Query, a nie tylko tych pochodzących ze skoroszytu programu Excel.

Przykładowe źródło danych dla tego pokazu to skoroszyt programu Excel z poniższą tabelą.

Przykładowe dane z programu Excel.

Ta tabela ze skoroszytu programu Excel zawiera błędy programu Excel, takie jak #NULL!, #REF!, i #DIV/0! w kolumnie Standardowa stawka . Podczas importowania tej tabeli do edytora Power Query na poniższej ilustracji pokazano, jak będzie ona wyglądać.

Przykładowa tabela w dodatku Power Query.

Zwróć uwagę, że błędy ze skoroszytu programu Excel są wyświetlane z wartością [Error] w każdej z komórek.

W tym artykule dowiesz się, jak zastąpić błąd inną wartością. Ponadto dowiesz się również, jak przechwycić błąd i użyć go do własnej konkretnej logiki.

W takim przypadku celem jest utworzenie nowej kolumny Stopa końcowa , która będzie używać wartości z kolumny Stawka Standardowa. Jeśli wystąpią jakiekolwiek błędy, użyje wartości z kolumny korespondenta stawki specjalnej.

Podaj alternatywną wartość podczas znajdowania błędów

W takim przypadku celem jest utworzenie nowej kolumny Stopa końcowa w przykładowym źródle danych, które będzie używać wartości z kolumny Rate Standard . Jeśli wystąpią jakiekolwiek błędy, użyje wartości z odpowiedniej kolumny Stawka specjalna.

Aby utworzyć nową kolumnę niestandardową, przejdź do menu Dodaj kolumnę i wybierz pozycję Kolumna niestandardowa. W oknie Kolumna niestandardowa wprowadź formułę try [Standard Rate] otherwise [Special Rate]. Nadaj nowej kolumnie nazwę Końcowa stawka.

Zrzut ekranu przedstawiający otwarte okno dialogowe Kolumna niestandardowa i formułę spróbuj w inny sposób wprowadzić w kolumnie niestandardowej.

Powyższa formuła spróbuje ocenić kolumnę Standard Rate (Stawka standardowa) i wyświetli jej wartość, jeśli nie zostaną znalezione żadne błędy. Jeśli błędy zostaną znalezione w kolumnie Stawka Standardowa, dane wyjściowe będą wartością zdefiniowaną po otherwise instrukcji , co w tym przypadku jest kolumną Stawka specjalna.

Po dodaniu poprawnych typów danych do wszystkich kolumn w tabeli na poniższej ilustracji przedstawiono wygląd ostatecznej tabeli.

Końcowa tabela spróbuj w przeciwnym razie.

Uwaga

Alternatywną metodą jest również wprowadzenie formuły try [Standard Rate] catch ()=> [Special Rate], która jest równoważna poprzedniej formule, ale przy użyciu słowa kluczowego catch z funkcją, która nie wymaga żadnych parametrów.

Słowo catch kluczowe zostało wprowadzone do dodatku Power Query w maju 2022 roku.

Podaj własną logikę błędów warunkowych

Używając tego samego przykładowego źródła danych co w poprzedniej sekcji, nowym celem jest utworzenie nowej kolumny dla stawki końcowej. Jeśli wartość z stawki standardowej istnieje, zostanie użyta ta wartość. W przeciwnym razie zostanie użyta wartość z kolumny Stawka specjalna, z wyjątkiem wierszy z dowolnym #REF! błędem.

Uwaga

Jedynym celem wykluczenia błędu #REF! jest w celach demonstracyjnych. Po przedstawieniu pojęć opisanych w tym artykule możesz wybrać dowolne wybrane pola z rekordu błędu.

Po wybraniu dowolnego odstępu obok wartości błędu zostanie wyświetlone okienko szczegółów w dolnej części ekranu. Okienko szczegółów zawiera zarówno przyczynę błędu, DataFormat.Errorjak i komunikat o błędzie: Invalid cell value '#REF!'

Zrzut ekranu przedstawiający wybrany błąd z komunikatem o błędzie w dolnej części okna dialogowego.

Jednocześnie można wybrać tylko jedną komórkę, dzięki czemu można skutecznie zobaczyć tylko składniki błędu o jednej wartości błędu jednocześnie. W tym miejscu utworzysz nową kolumnę niestandardową i użyjesz try wyrażenia .

Używanie try z logiką niestandardową

Aby utworzyć nową kolumnę niestandardową, przejdź do menu Dodaj kolumnę i wybierz pozycję Kolumna niestandardowa. W oknie Kolumna niestandardowa wprowadź formułę try [Standard Rate]. Nadaj nowej kolumnie nazwę Wszystkie błędy.

Zrzut ekranu przedstawiający otwarte okno dialogowe Kolumna niestandardowa i formułę try wprowadzoną w kolumnie niestandardowej.

Wyrażenie try konwertuje wartości i błędy na wartość rekordu, która wskazuje, czy try wyrażenie obsłużyło błąd, czy nie, a także odpowiednią wartość lub rekord błędu.

Spróbuj użyć wartości rekordów.

Możesz rozwinąć tę nowo utworzoną kolumnę z wartościami rekordów i przyjrzeć się dostępnym polam, które mają zostać rozwinięte, wybierając ikonę obok nagłówka kolumny.

Zrzut ekranu przedstawiający kolumnę Wszystkie błędy z podkreśloną ikoną rozwijania oraz zaznaczonymi polami HasError, Value i Error.

Ta operacja uwidacznia trzy nowe pola:

  • Wszystkie błędy.HasError — wyświetla, czy wartość z kolumny Rate w warstwie Standardowa zawiera błąd, czy nie.
  • Wszystkie błędy.Value — jeśli wartość z kolumny Standard Rate nie zawiera błędu, ta kolumna wyświetli wartość z kolumny Standard Rate (Stawka Standardowa). W przypadku wartości z błędami to pole nie będzie dostępne, a podczas operacji rozwijania ta kolumna będzie zawierać null wartości.
  • Wszystkie błędy.Error — jeśli wartość z kolumny Standard Rate zawiera błąd, ta kolumna wyświetli rekord błędu dla wartości z kolumny Rate Standard. W przypadku wartości bez błędów to pole nie będzie dostępne, a podczas operacji rozwijania ta kolumna będzie zawierać null wartości.

Zrzut ekranu przedstawiający tabelę z nowymi polami w kolumnach z wybraną wartością All.Errors.Error i wyświetlanymi komunikatami o błędach w dolnej części tabeli.

Aby dokładniej zbadać, możesz rozwinąć kolumnę Wszystkie błędy.Error , aby uzyskać trzy składniki rekordu błędu:

  • Przyczyna błędu
  • Komunikat o błędzie
  • Szczegóły błędu

Po wykonaniu operacji rozwijania pole Wszystkie błędy.Error.Message wyświetla określony komunikat o błędzie informujący o dokładnie tym, jaki błąd programu Excel ma każda komórka. Komunikat o błędzie pochodzi z pola Komunikat o błędzie rekordu błędu.

Zrzut ekranu przedstawiający wyświetlane określone komunikaty o błędach.

Teraz z każdym komunikatem o błędzie w nowej kolumnie można utworzyć nową kolumnę warunkową o nazwie Stopa końcowa i następujące klauzule:

  • Jeśli wartość w kolumnie All Errors.Errors.Message jest nullrówna , dane wyjściowe będą wartością z kolumny Rate Standard .
  • W przeciwnym razie, jeśli wartość w kolumnie All Errors.Errors.Message nie jest równa Invalid cell value '#REF!'., dane wyjściowe będą wartością z kolumny Stawka specjalna.
  • W przeciwnym razie wartość null.

Zrzut ekranu przedstawiający okno dialogowe Dodawanie kolumny warunkowej ze wszystkimi warunkami błędu ustawionymi dla nowej kolumny.

Po zachowaniu tylko kolumn Account, Standard Rate, Special Rate i Final Rate oraz dodaniu poprawnego typu danych dla każdej kolumny na poniższej ilustracji pokazano, jak wygląda końcowa tabela.

Końcowa tabela z typami danych.

Używanie i trycatch z logiką niestandardową

Alternatywnie możesz również utworzyć nową kolumnę niestandardową przy użyciu try słów kluczowych i catch .

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

Okno dialogowe kolumny niestandardowej z nową formułą pokazującą metodę try i catch składni.

Więcej zasobów