{"id":182564,"date":"2024-03-27T03:03:00","date_gmt":"2024-03-27T02:03:00","guid":{"rendered":"https:\/\/liora.io\/en\/?p=182564"},"modified":"2026-08-07T10:25:40","modified_gmt":"2026-08-07T09:25:40","slug":"power-query-error-handling-how-does-error-handling-work","status":"publish","type":"post","link":"https:\/\/liora.io\/en\/power-query-error-handling-how-does-error-handling-work","title":{"rendered":"Power Query Error Handling: How does error handling work?"},"content":{"rendered":"\n<p><strong>#NULL!, #REF!, #DIV\/0!, N\/A &#8230;. Data tables regularly contain errors that limit data analysis. Fortunately, it&#8217;s possible to correct them through Power Query Error Handling. Liora shows you how.<\/strong><\/p>\n\n\n<h2 class=\"wp-block-heading\" id=\"power-query-error-handling\"> Power Query Error Handling<\/h2>\n\n\n<p>There are two main types of error in the<a href=\"https:\/\/liora.io\/en\/power-query-what-is-it-whats-it-for\"> Power Query<\/a> editor: step and column errors.<\/p>\n\n\n<h3 class=\"wp-block-heading\" id=\"stage-level\">Stage level<\/h3>\n\n\n<p>If an error occurs in the step, the request cannot be loaded. You will then see an error message appear in a yellow window.<\/p>\n\n\n<p>This provides information on the various components of the error: reason, message and details<\/p>\n\n\n<p>And if you need more information, you can also click on the &#8220;Access error&#8221; button. It&#8217;s all this data that enables you to adopt effective error management in <strong>Power Query.<\/strong><\/p>\n\n\n<p>Here are the most common errors:<\/p>\n\n\n<ul class=\"wp-block-list\"><li><strong>Source not found:<\/strong> this error appears if you can&#8217;t access the data source, if you don&#8217;t have identification information or if the source has been moved. In such cases, you should modify the access path.<\/li><li><strong>Column not found:<\/strong> this is what happens if you&#8217;ve specified a column that doesn&#8217;t exist in the query. There are several possible solutions here, including deleting the step referring to the column in question.<\/li><li><strong>Formula.firewall:<\/strong> this error occurs when you combine or merge data from several sources. Before applying a solution, you need to understand the exact cause of the problem by accessing the data confidentiality firewall.<\/li><\/ul>\n\n\n<h3 class=\"wp-block-heading\" id=\"column-level\">Column level<\/h3>\n\n\n<p>In this case, the <strong>query loads, but the value appears as an error in the corresponding cell.<\/strong> To understand the origin of the error, you can click on the white space of the cell concerned (a new yellow window will appear) or use the data profiling tools.<\/p>\n\n\n<p>These errors can be linked to a data type conversion problem, unsupported operations, nested structured values, etc.<\/p>\n\n\n<p>Whatever the problem, there are a number of error management solutions available with<strong> Power Query.<\/strong> Here&#8217;s what you need to know:<\/p>\n\n\n<ul class=\"wp-block-list\"><li><strong>Deleting errors:<\/strong> select the column concerned, then go to the &#8220;Reduce rows&#8221; group under the &#8220;Home&#8221; tab. Click on &#8220;Delete rows&#8221;, then on &#8220;Delete errors&#8221;.<\/li><li><strong>Replacing errors:<\/strong> select the column concerned, then go to the &#8220;Any column&#8221; group under the &#8220;Transform&#8221; tab. Click on &#8220;Replace values&#8221;, then on &#8220;Replace errors&#8221;. Then enter the new value (as an integer, custom text, etc.).<\/li><li><strong>Keeping errors:<\/strong> this makes it easier to identify lines containing errors. The procedure is as follows: Home &gt; Reduce lines &gt; Keep lines &gt; Keep errors.<\/li><\/ul>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:725px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for Column level\" decoding=\"async\" height=\"600\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/2023\/10\/erreurs_Power_Query1.jpg\" style=\"width:725px;max-width:100%;height:auto\" width=\"725\"\/><\/figure>\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center wp-container-core-buttons-is-layout-5ee10de4\" style=\"margin-top:32px;margin-bottom:32px\"><div class=\"wp-block-button\"><a class=\"wp-block-button__link wp-element-button\" href=\"\/en\/courses\/data-ai\/power-bi\">Learn how to handle errors in Power Query<\/a><\/div><\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"customized-power-query-error-handling\">Customized  Power Query Error Handling<\/h2>\n\n\n<p>Beyond the above solutions, it&#8217;s also possible to handle errors with the <strong>Power Query editor<\/strong> by adopting its own logic. <\/p>\n\n\n<p>Here&#8217;s how to do it:<\/p>\n\n\n<ul class=\"wp-block-list\"><li><strong>Replace an error with another value<\/strong>: to do this, simply add a custom column with different options. So, in the &#8220;Custom column&#8221; window, you can enter the query:<pre>try [column-1] otherwise [Column-2]<\/pre><p>If no error is found in column-1, its data will appear. Otherwise, the data in column-2 will appear.<\/p><\/li><li><strong>Customize new results <\/strong>: again, you need to use the custom column, but the query differs:<pre>try [column-1] catch (r)=&gt; if r[Message] &lt;&gt; \"Invalid cell value '#REF!'.\" then [column-2] else null. <\/pre><p>In this case, column-1 will be replaced by column-2 only if the error message is different from #REF! ( #NULL! or #DIV\/0!). Otherwise, column-1 will be replaced by &#8220;null&#8221;.<\/p><\/li><\/ul>\n\n\n<p>To effectively manage all these errors with Power Query, it&#8217;s best to get trained. With Liora, you can do just that. Through our programs, you&#8217;ll learn to master the query editor and numerous data tables. Join our training courses.<\/p>\n\n\n<h2 class=\"wp-block-heading\" id=\"things-to-remember\">Things to remember :<\/h2>\n\n\n<ul class=\"wp-block-list\"><li>Given the sheer volume of data contained within a table, errors are very common. This can limit data processing and analysis.<\/li><li>Fortunately, efficient error management is possible with Power Query.<\/li><li>The query editor provides several manipulations to correct bugs according to the type of error.<\/li><li>You can also customize the correction options to suit your needs.<\/li><\/ul>\n\n\n<figure class=\"wp-block-image size-full\" style=\"margin-top:32px;margin-bottom:32px\"><img alt=\"Illustration for Things to remember :\" decoding=\"async\" height=\"600\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/2023\/10\/erreurs_Power_Query2.jpg\" style=\"width:100%;height:auto\" width=\"754\"\/><\/figure>\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center wp-container-core-buttons-is-layout-5ee10de4\" style=\"margin-top:32px;margin-bottom:32px\"><div class=\"wp-block-button\"><a class=\"wp-block-button__link wp-element-button\" href=\"\/en\/courses\/data-ai\/power-bi\">Power Query training<\/a><\/div><\/div>\n\n","protected":false},"excerpt":{"rendered":"<p>#NULL!, #REF!, #DIV\/0!, N\/A &#8230;. Data tables regularly contain errors that limit data analysis. Fortunately, it&#8217;s possible to correct them through Power Query Error Handling. Liora shows you how. Power Query Error Handling There are two main types of error in the Power Query editor: step and column errors. Stage level If an error occurs [&hellip;]<\/p>\n","protected":false},"author":76,"featured_media":182566,"comment_status":"open","ping_status":"open","sticky":false,"template":"elementor_theme","format":"standard","meta":{"_acf_changed":false,"editor_notices":[],"footnotes":""},"categories":[2433],"class_list":["post-182564","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-ai"],"acf":[],"_links":{"self":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/182564","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/users\/76"}],"replies":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/comments?post=182564"}],"version-history":[{"count":2,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/182564\/revisions"}],"predecessor-version":[{"id":209750,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/182564\/revisions\/209750"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media\/182566"}],"wp:attachment":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media?parent=182564"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/categories?post=182564"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}