{"id":71314,"date":"2017-06-09T11:19:32","date_gmt":"2017-06-09T11:19:32","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=71314"},"modified":"2021-09-07T13:41:08","modified_gmt":"2021-09-07T13:41:08","slug":"filtering-errors-in-powerbi","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/other\/filtering-errors-in-powerbi\/","title":{"rendered":"Filtering Errors in PowerBI"},"content":{"rendered":"

I just faced this problem when trying to analyze some information from google analytics in PowerBI<\/strong>. In my specific situation, I was trying to extract a number from some URL querystrings and filter the rows without the number. When I set the data type of the column to decimal, the rows without a number receive an expression error.\u00a0Let’s build an example filtering errors in PowerBI<\/strong>.<\/p>\n

The image below illustrate a small set of data in excel that will result in this problem.\u00a0The objective is to extract the numbers and exclude all the rows without numbers.\u00a0<\/p>\n

\"First<\/p>\n

After importing the excel file in PowerBI<\/strong>, let’s extract all the content after the “=”<\/em> sign using Transform<\/strong> => Extract<\/strong>=> Text After Delimiter.\u00a0<\/strong><\/p>\n

\"Extracting<\/p>\n

\"defining<\/p>\n

\"middle<\/p>\n

Now, let’s change the data type to decimal using Transform<\/strong> => Data Type<\/strong><\/p>\n

 <\/p>\n

\"changing<\/p>\n

 <\/p>\n

We have two errors in this resultset, as you may notice in the images below. There is no way to exclude these errors using the interface, we need to code in M<\/strong> to achieve this. There are two useful functions available to filter errors: Table.RemoveRowsWithErrors<\/strong> and Table.SelectRowsWithErrors<\/strong>, the names already explain what they do.<\/p>\n

\"table<\/p>\n

\"error<\/p>\n

We need to open the Advanced Editor<\/strong>, inside View<\/strong>, to edit the M<\/strong> code.<\/p>\n

\"Editing<\/p>\n

\"Initial<\/p>\n

 <\/p>\n

We will add a new line before the “In”<\/em> statement. In this line, we will create a new name for our result, like this:<\/p>\n

#\"Result without errors\" =<\/pre>\n
After the “=”<\/em> sign we can use the function, it has two parameters: The name of the result of the previous line and the column we want to check for errors.<\/p>\n
#\"Result without errors\" =Table.RemoveRowsWithErrors(#\"Changed Type1\", {\"Column1\"})<\/pre>\n
Two more details to go:<\/p>\n
    \n
  • The previous line need an extra comma at the end<\/li>\n
  • After the “in” statement, we need to change the name of the final result set to our new name<\/li>\n<\/ul>\n
    in\r\n   #\"Result without errors\"<\/pre>\n
     <\/p>\n
    \"final<\/p>\n
     <\/p>\n
    The final result, without the errors:<\/p>\n
    \"Final<\/p>\n","protected":false},"excerpt":{"rendered":"
    I just faced this problem when trying to analyze some information from google analytics in PowerBI. In my specific situation, I was trying to extract a number from some URL querystrings and filter the rows without the number. When I set the data type of the column to decimal, the rows without a number receive……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6810],"class_list":["post-71314","post","type-post","status-publish","format-standard","hentry","category-other"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71314","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=71314"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71314\/revisions"}],"predecessor-version":[{"id":92372,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71314\/revisions\/92372"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=71314"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=71314"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=71314"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=71314"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}