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. Ten artykuł koncentruje się na tym, jak można przechwytywać błędy i zarządzać nimi na podstawie własnej konkretnej logiki.
Uwaga
Aby zademonstrować tę koncepcję, w tym artykule użyto 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ą.
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 wygląda.
Zwróć uwagę, że błędy ze skoroszytu programu Excel są wyświetlane z wartością [Error]
w każdej z komórek.
Z tego artykułu dowiesz się, jak zastąpić błąd inną wartością. Ponadto dowiesz się również, jak przechwycić błąd i użyć go dla własnej konkretnej logiki.
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 używa wartości z kolumny Standard Rate (Stawka standardowa). Jeśli wystąpią jakiekolwiek błędy, zostanie użyta wartość 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.
Ta formuła próbuje ocenić kolumnę Standard Rate i wyświetli jej wartość, jeśli nie znaleziono żadnych błędów. Jeśli błędy zostaną znalezione w kolumnie Stawka Standardowa, dane wyjściowe są wartością zdefiniowaną po otherwise
instrukcji, która 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.
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 jest używana 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.Error
jak i komunikat o błędzie: Invalid cell value '#REF!'
Jednocześnie można wybrać tylko jedną komórkę, dzięki czemu można skutecznie zbadać tylko składniki błędu o jednej wartości błędu jednocześnie. Na tym etapie 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.
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, oraz odpowiednią wartość lub rekord błędu.
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.
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świetla wartość z kolumny Standard Rate (Stawka Standardowa). W przypadku wartości z błędami to pole jest niedostępne, a podczas operacji rozwijania ta kolumna zawiera
null
wartości. - Wszystkie błędy.Error — jeśli wartość z kolumny Standard Rate zawiera błąd, ta kolumna wyświetla rekord błędu dla wartości z kolumny Rate Standard . W przypadku wartości bez błędów to pole jest niedostępne, a podczas operacji rozwijania ta kolumna zawiera
null
wartości.
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.
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
null
równa , dane wyjściowe są 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 są wartością z kolumny Stawka specjalna. - W przeciwnym razie wartość null.
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.
Używanie i try
catch
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