{"id":1495,"date":"2020-07-15T07:05:40","date_gmt":"2020-07-15T07:05:40","guid":{"rendered":"http:\/\/bi.unija.com\/en\/?p=1495"},"modified":"2020-08-17T13:53:31","modified_gmt":"2020-08-17T13:53:31","slug":"dynamic-filtering-using-a-list-in-power-query","status":"publish","type":"post","link":"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/","title":{"rendered":"Dynamic filtering using a list in Power Query"},"content":{"rendered":"

For Power BI version 2.76.5678.782 (December 2019).<\/em><\/p>\n

In shaping data, we often need to compare two tables and filter one based on the other. In this article we’ll show how to remove the rows from a table, that appear in another table.<\/p>\n

Let’s look at the following example: we want to filter Sales<\/strong> table, so that we’re left with all genres in Genre<\/strong> field, except for genres Party<\/strong>, Platform<\/strong> in Puzzle<\/strong>. We use the following steps.<\/p>\n

1. Open Power Query with Transform Data<\/strong>.<\/p>\n

2. Select the filter in Genre<\/strong> field and check Select All<\/strong>. Uncheck genres Party, Platform<\/strong> in Puzzle<\/strong>. Confirm with OK<\/strong>.<\/p>\n

<\/p>\n

3. Select and open Advanced Editor<\/strong>. We see the step saved as<\/p>\n

#”Filtered Rows” = Table.SelectRows(#”Renamed Columns1″, <\/strong><\/p>\n

each ([Genre] <> “Party” <\/strong><\/p>\n

and [Genre] <> “Platform” <\/strong><\/p>\n

and [Genre] <> “Puzzle”))<\/strong><\/p>\n

<\/p>\n

4. We’ll edit the step to take the list as an argument instead of elements Party, Platform<\/strong> in Puzzle.<\/strong><\/p>\n

5. We’ll enter data we want to filter into a table and convert the table into a list. To create the table select Enter Data<\/strong> and type in values Party, Platform<\/strong> in Puzzle. <\/strong>We name the table GenreList. <\/strong>Confirm with OK<\/strong>.<\/p>\n

<\/p>\n

6. Convert the table to a list by selecting Transform > Convert to List<\/strong>.<\/p>\n

<\/p>\n

7. We don’t need to create table manually, we could use a table from any other queries.<\/p>\n

8. Edit the formula in the filtering step of the query. The formula is now:<\/p>\n

#”Filtered Rows” = Table.SelectRows(#”Renamed Columns1″, <\/strong><\/p>\n

each List.Contains(GenreList, [Genre]) = false)<\/strong><\/p>\n

Confirm with OK<\/strong>.<\/p>\n

<\/p>\n

This step will now check if the element is found on the GenreList<\/strong> for every element of the table. After filtering we’ll be only left with elements that are not on the list.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

For Power BI version 2.76.5678.782 (December 2019). In shaping data, we often need to compare two tables and filter one based on the other. In this article we’ll show how to remove the rows from a table, that appear in<\/p>\n","protected":false},"author":5,"featured_media":1212,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[4],"tags":[9],"class_list":["post-1495","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-advanced","tag-power-query"],"acf":[],"yoast_head":"\nDynamic filtering using a list in Power Query - Bi Unija<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dynamic filtering using a list in Power Query - Bi Unija\" \/>\n<meta property=\"og:description\" content=\"For Power BI version 2.76.5678.782 (December 2019). In shaping data, we often need to compare two tables and filter one based on the other. In this article we’ll show how to remove the rows from a table, that appear in\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/\" \/>\n<meta property=\"og:site_name\" content=\"Bi Unija\" \/>\n<meta property=\"article:published_time\" content=\"2020-07-15T07:05:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-08-17T13:53:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG14.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"720\" \/>\n\t<meta property=\"og:image:height\" content=\"480\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Branka Trifunovi\u0107\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Branka Trifunovi\u0107\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/\",\"url\":\"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/\",\"name\":\"Dynamic filtering using a list in Power Query - Bi Unija\",\"isPartOf\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG14.jpg\",\"datePublished\":\"2020-07-15T07:05:40+00:00\",\"dateModified\":\"2020-08-17T13:53:31+00:00\",\"author\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\"},\"breadcrumb\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/#primaryimage\",\"url\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG14.jpg\",\"contentUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG14.jpg\",\"width\":720,\"height\":480},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/bi.unija.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dynamic filtering using a list in Power Query\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/bi.unija.com\/en\/#website\",\"url\":\"https:\/\/bi.unija.com\/en\/\",\"name\":\"Bi Unija\",\"description\":\"Just another Bi site\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/bi.unija.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\",\"name\":\"Branka Trifunovi\u0107\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g\",\"caption\":\"Branka Trifunovi\u0107\"},\"url\":\"https:\/\/bi.unija.com\/en\/author\/branka\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Dynamic filtering using a list in Power Query - Bi Unija","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/","og_locale":"en_US","og_type":"article","og_title":"Dynamic filtering using a list in Power Query - Bi Unija","og_description":"For Power BI version 2.76.5678.782 (December 2019). In shaping data, we often need to compare two tables and filter one based on the other. In this article we’ll show how to remove the rows from a table, that appear in","og_url":"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/","og_site_name":"Bi Unija","article_published_time":"2020-07-15T07:05:40+00:00","article_modified_time":"2020-08-17T13:53:31+00:00","og_image":[{"width":720,"height":480,"url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG14.jpg","type":"image\/jpeg"}],"author":"Branka Trifunovi\u0107","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Branka Trifunovi\u0107","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/","url":"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/","name":"Dynamic filtering using a list in Power Query - Bi Unija","isPartOf":{"@id":"https:\/\/bi.unija.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/#primaryimage"},"image":{"@id":"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/#primaryimage"},"thumbnailUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG14.jpg","datePublished":"2020-07-15T07:05:40+00:00","dateModified":"2020-08-17T13:53:31+00:00","author":{"@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10"},"breadcrumb":{"@id":"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/#primaryimage","url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG14.jpg","contentUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG14.jpg","width":720,"height":480},{"@type":"BreadcrumbList","@id":"https:\/\/bi.unija.com\/en\/dynamic-filtering-using-a-list-in-power-query\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bi.unija.com\/en\/"},{"@type":"ListItem","position":2,"name":"Dynamic filtering using a list in Power Query"}]},{"@type":"WebSite","@id":"https:\/\/bi.unija.com\/en\/#website","url":"https:\/\/bi.unija.com\/en\/","name":"Bi Unija","description":"Just another Bi site","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/bi.unija.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10","name":"Branka Trifunovi\u0107","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ec99f651bcc98a163161515ec254b974?s=96&d=mm&r=g","caption":"Branka Trifunovi\u0107"},"url":"https:\/\/bi.unija.com\/en\/author\/branka\/"}]}},"_links":{"self":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1495"}],"collection":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/comments?post=1495"}],"version-history":[{"count":3,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1495\/revisions"}],"predecessor-version":[{"id":1518,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1495\/revisions\/1518"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media\/1212"}],"wp:attachment":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media?parent=1495"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/categories?post=1495"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/tags?post=1495"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}