Foutafhandeling
Net als bij de manier waarop Excel en de DAX-taal een IFERROR functie hebben, heeft Power Query een eigen syntaxis om fouten te testen en te ondervangen.
Zoals vermeld in het artikel over het omgaan met fouten in Power Query, kunnen fouten worden weergegeven op stap- of celniveau. In dit artikel wordt uitgelegd hoe u fouten kunt ondervangen en beheren op basis van uw eigen specifieke logica.
Notitie
Om dit concept te demonstreren, gebruikt dit artikel een Excel-werkmap als gegevensbron. De concepten die hier worden weergegeven, zijn van toepassing op alle waarden in Power Query en niet alleen op de waarden die afkomstig zijn van een Excel-werkmap.
De voorbeeldgegevensbron voor deze demonstratie is een Excel-werkmap met de volgende tabel.
Deze tabel uit een Excel-werkmap bevat Excel-fouten zoals #NULL!, #REF! en #DIV/0! in de kolom Standaardtarief . Wanneer u deze tabel in de Power Query-editor importeert, ziet u in de volgende afbeelding hoe deze eruitziet.
U ziet hoe de fouten uit de Excel-werkmap worden weergegeven met de [Error] waarde in elk van de cellen.
In dit artikel leert u hoe u een fout vervangt door een andere waarde. Daarnaast leert u ook hoe u een fout onderscheppen en gebruikt voor uw eigen specifieke logica.
In dit geval is het doel om een nieuwe kolom Eindtarief te maken die gebruikmaakt van de waarden uit de kolom Standaardtarief . Als er fouten zijn, wordt de waarde uit de kolom Speciale tarief van de correspondent gebruikt.
Geef een alternatieve waarde op bij het vinden van fouten
In dit geval is het doel om een nieuwe kolom Definitief tarief te maken in de voorbeeldgegevensbron die de waarden uit de kolom Standaardtarief gebruikt. Als er fouten zijn, wordt de waarde uit de corresponderende kolom Speciale tarieven gebruikt.
Als u een nieuwe aangepaste kolom wilt maken, gaat u naar het menu Kolom toevoegen en selecteert u Aangepaste kolom. Voer in het venster Aangepaste kolom de formule try [Standard Rate] otherwise [Special Rate]in. Geef deze nieuwe kolom de definitieve rente.
In de bovenstaande formule wordt geprobeerd de kolom Standaardsnelheid te evalueren en wordt de waarde ervan uitgevoerd als er geen fouten worden gevonden. Als er fouten worden aangetroffen in de kolom Standaardtarief , is de uitvoer de waarde die is gedefinieerd na de otherwise instructie, wat in dit geval de kolom Speciale tarieven is.
Nadat u de juiste gegevenstypen hebt toegevoegd aan alle kolommen in de tabel, ziet u in de volgende afbeelding hoe de uiteindelijke tabel eruitziet.
Notitie
Als alternatief kunt u ook de formule try [Standard Rate] catch ()=> [Special Rate]invoeren, die gelijk is aan de vorige formule, maar het trefwoord catch gebruiken met een functie waarvoor geen parameters zijn vereist.
Het catch trefwoord is in mei 2022 geïntroduceerd in Power Query.
Uw eigen logica voor voorwaardelijke fouten opgeven
Met behulp van dezelfde voorbeeldgegevensbron als in de vorige sectie, is het nieuwe doel om een nieuwe kolom te maken voor het uiteindelijke tarief. Als de waarde van het standaardtarief bestaat, wordt die waarde gebruikt. Anders wordt de waarde uit de kolom Speciaal tarief gebruikt, met uitzondering van de rijen met een #REF! fout.
Notitie
Het enige doel van het uitsluiten van de #REF! fout is voor demonstratiedoeleinden. Met de concepten die in dit artikel worden weergegeven, kunt u alle velden van uw keuze instellen in de foutrecord.
Wanneer u een van de witruimten naast de foutwaarde selecteert, wordt het detailvenster onder aan het scherm weergegeven. Het detailvenster bevat zowel de foutreden als DataFormat.Errorhet foutbericht: Invalid cell value '#REF!'
U kunt slechts één cel tegelijk selecteren, zodat u alleen de foutonderdelen van één foutwaarde tegelijk kunt zien. Hier maakt u een nieuwe aangepaste kolom en gebruikt u de try expressie.
Gebruiken try met aangepaste logica
Als u een nieuwe aangepaste kolom wilt maken, gaat u naar het menu Kolom toevoegen en selecteert u Aangepaste kolom. Voer in het venster Aangepaste kolom de formule try [Standard Rate]in. Geef deze nieuwe kolom alle fouten een naam.
De try expressie converteert waarden en fouten naar een recordwaarde die aangeeft of de try expressie een fout heeft verwerkt, evenals de juiste waarde of de foutrecord.
U kunt deze zojuist gemaakte kolom uitbreiden met recordwaarden en de beschikbare velden bekijken die moeten worden uitgevouwen door het pictogram naast de kolomkop te selecteren.
Met deze bewerking worden drie nieuwe velden weergegeven:
- Alle errors.HasError: geeft aan of de waarde uit de kolom Standaardsnelheid een fout heeft gehad of niet.
- Alle errors.value: als de waarde uit de kolom Standaardtarief geen fout heeft, wordt in deze kolom de waarde uit de kolom Standaardtarief weergegeven. Voor waarden met fouten is dit veld niet beschikbaar en tijdens de uitvouwbewerking bevat
nulldeze kolom waarden. - Alle errors.error: als de waarde uit de kolom Standaardsnelheid een fout heeft, wordt in deze kolom de foutrecord voor de waarde uit de kolom Standaardtarief weergegeven. Voor waarden zonder fouten is dit veld niet beschikbaar. Tijdens de uitvouwbewerking bevat
nulldeze kolom waarden.
Voor verder onderzoek kunt u de kolom Alle fouten.Fout uitbreiden om de drie onderdelen van de foutrecord op te halen:
- Reden voor de fout
- Foutbericht
- Foutdetails
Nadat u de uitvouwbewerking hebt uitgevoerd, wordt in het veld Alle fouten.Error.Message het specifieke foutbericht weergegeven dat aangeeft welke Excel-fout elke cel heeft. Het foutbericht wordt afgeleid van het veld Foutbericht van de foutrecord.
Met elk foutbericht in een nieuwe kolom kunt u nu een nieuwe voorwaardelijke kolom maken met de naam Definitief tarief en de volgende componenten:
- Als de waarde in de kolom All Errors.Errors.Message gelijk is
nullaan, is de uitvoer de waarde uit de kolom Standaardtarief . - Als de waarde in de kolom All Errors.Errors.Message niet gelijk is
Invalid cell value '#REF!'., is de uitvoer de waarde uit de kolom Speciale tarieven . - Anders, null.
Nadat u alleen de kolommen Account, Standaardtarief, Speciaal tarief en Eindtarief hebt behouden en het juiste gegevenstype voor elke kolom hebt toegevoegd, ziet u in de volgende afbeelding hoe de uiteindelijke tabel eruitziet.
Gebruik try en catch met aangepaste logica
U kunt ook een nieuwe aangepaste kolom maken met behulp van de try en catch trefwoorden.
try [Standard Rate] catch (r)=> if r[Message] <> "Invalid cell value '#REF!'." then [Special Rate] else null
