{"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-02-06T08:16:01","modified_gmt":"2026-02-06T07:16:01","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.elementor-heading-title{padding:0;margin:0;line-height:1}.elementor-widget-heading .elementor-heading-title[class*=elementor-size-]&gt;a{color:inherit;font-size:inherit;line-height:inherit}.elementor-widget-heading .elementor-heading-title.elementor-size-small{font-size:15px}.elementor-widget-heading .elementor-heading-title.elementor-size-medium{font-size:19px}.elementor-widget-heading .elementor-heading-title.elementor-size-large{font-size:29px}.elementor-widget-heading .elementor-heading-title.elementor-size-xl{font-size:39px}.elementor-widget-heading .elementor-heading-title.elementor-size-xxl{font-size:59px}<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>\t\t\n\t\t\t<h3> Power Query Error Handling<\/h3>\t\t\n\t\t<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>\t\t\n\t\t\t<h4>Stage level<\/h4>\t\t\n\t\t<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><p>This provides information on the various components of the error: reason, message and details<\/p><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><p>Here are the most common errors:<\/p><ul><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>\t\t\n\t\t\t<h4>Column level<\/h4>\t\t\n\t\t<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><p>These errors can be linked to a data type conversion problem, unsupported operations, nested structured values, etc.<\/p><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><ul><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>\t\t\n\t\t\t\n.elementor-widget-image{text-align:center}.elementor-widget-image a{display:inline-block}.elementor-widget-image a img[src$=&#8221;.svg&#8221;]{width:48px}.elementor-widget-image img{vertical-align:middle;display:inline-block}\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" src=\"https:\/\/liora.io\/app\/uploads\/2023\/10\/erreurs_Power_Query1.jpg\" title=\"\" alt=\"\" loading=\"lazy\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center\"><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\t\t\t<h3>Customized  Power Query Error Handling<\/h3>\t\t\n\t\t<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><p>Here&#8217;s how to do it:<\/p><ul><li style=\"font-weight: 400\"><b>Replace an error with another value<\/b>: 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><b>Customize new results <\/b>: 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><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>\t\t\n\t\t\t<h3>Things to remember :<\/h3>\t\t\n\t\t<ul><li style=\"font-weight: 400\">Given the sheer volume of data contained within a table, errors are very common. This can limit data processing and analysis.<\/li><li style=\"font-weight: 400\">Fortunately, efficient error management is possible with Power Query.<\/li><li style=\"font-weight: 400\">The query editor provides several manipulations to correct bugs according to the type of error.<\/li><li style=\"font-weight: 400\">You can also customize the correction options to suit your needs.<\/li><\/ul>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" src=\"https:\/\/liora.io\/app\/uploads\/2023\/10\/erreurs_Power_Query2.jpg\" title=\"\" alt=\"\" loading=\"lazy\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center\"><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","protected":false},"excerpt":{"rendered":"<p>.elementor-heading-title{padding:0;margin:0;line-height:1}.elementor-widget-heading .elementor-heading-title[class*=elementor-size-]&gt;a{color:inherit;font-size:inherit;line-height:inherit}.elementor-widget-heading .elementor-heading-title.elementor-size-small{font-size:15px}.elementor-widget-heading .elementor-heading-title.elementor-size-medium{font-size:19px}.elementor-widget-heading .elementor-heading-title.elementor-size-large{font-size:29px}.elementor-widget-heading .elementor-heading-title.elementor-size-xl{font-size:39px}.elementor-widget-heading .elementor-heading-title.elementor-size-xxl{font-size:59px} #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 [&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":1,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/182564\/revisions"}],"predecessor-version":[{"id":205897,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/182564\/revisions\/205897"}],"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}]}}