{"id":1255,"date":"2020-06-14T20:20:56","date_gmt":"2020-06-14T20:20:56","guid":{"rendered":"http:\/\/bi.unija.com\/en\/?p=1255"},"modified":"2020-08-17T13:51:49","modified_gmt":"2020-08-17T13:51:49","slug":"dynamic-removal-of-rows-in-power-query","status":"publish","type":"post","link":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/","title":{"rendered":"Dynamic Removal of Rows in Power Query"},"content":{"rendered":"

In this article we\u2019ll see how to remove appropriate number of irrelevant rows in Power Query editor. <\/em><\/p>\n

We often work with data that starts with description like time of creation, location, author, etc. This metadata usually takes up first few rows in our data. We can remove it by opening the data in Power Query, selecting Remove Rows > Remove Top Rows <\/strong>and entering the desired number of rows to remove. This becomes time consuming when we have a lot of similar files to clean. Fortunately, we can create a query that detects and removes all the irrelevant rows.<\/p>\n

Example: removing rows from a text file<\/h1>\n

In our example we\u2019ll use a .csv list of all the Titanic passengers. We open the data in Power BI with Get Data <\/strong>and selecting the file. We get the following preview.<\/p>\n

<\/p>\n

We choose Comma as Delimiter and open the Power Query editor with Transform Data<\/strong>. We duplicate the first step in Applied Steps<\/strong> and name it Base. We\u2019ll refer to this step later.<\/p>\n

<\/p>\n

We add an index column: we select Add Column ><\/strong> Index Column ><\/strong> From 1<\/strong>. We move the new column to the left end of the table, where we can easily see it.<\/p>\n

<\/p>\n

We need the first cell where our data starts, in our case it\u2019s the cell with the text PassengerId. We right-click on the second column and select filter, Text Filters<\/strong> > Equals,<\/strong> enter the text PassengerId and confirm with Ok. Table is filtered and we\u2019re left with a single row. We right click on the cell in the first column and select Drill Down<\/strong>. We get the number value, in our case 5.<\/p>\n

<\/p>\n

We found the row number where our data starts. We now call the original table: we add a new step with by right-clicking on the last step and selecting Insert Step After<\/strong>. We enter the name Base in the formula bar of this new step.<\/p>\n

<\/p>\n

We have our Base table again. We remove couple of first rows of the table by selecting Remove Rows ><\/strong> Remove Top Rows<\/strong>. We enter any number, for example 7, and confirm with Ok<\/strong>. We then change the formula of the step, instead of 7 we type in Index-1 <\/strong>(since our data starts in row numbered Index, we want to remove Index-1 rows).<\/p>\n

<\/p>\n

The step is now dynamic: it will always remove all the rows that are above the cell with the text PassengerId. We finish preparing our data with selecting Use First Row As Headers<\/strong>.<\/p>\n

Function for Dynamic Removal of Data<\/h1>\n

We usually want to remove unnecessary rows from all the files. We can save all the steps we made in this article to a function and use the function on all the files we need. We\u2019ll go through the process of making the function in our next article, Dynamic Removal of Rows in Power Query for All the Files in a Folder<\/a>.<\/p>\n

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

In this article we\u2019ll see how to remove appropriate number of irrelevant rows in Power Query editor. We often work with data that starts with description like time of creation, location, author, etc. This metadata usually takes up first few<\/p>\n","protected":false},"author":5,"featured_media":1205,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[3],"tags":[9],"class_list":["post-1255","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-intermediate","tag-power-query"],"acf":[],"yoast_head":"\nDynamic Removal of Rows 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-removal-of-rows-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 Removal of Rows in Power Query - Bi Unija\" \/>\n<meta property=\"og:description\" content=\"In this article we\u2019ll see how to remove appropriate number of irrelevant rows in Power Query editor. We often work with data that starts with description like time of creation, location, author, etc. This metadata usually takes up first few\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/\" \/>\n<meta property=\"og:site_name\" content=\"Bi Unija\" \/>\n<meta property=\"article:published_time\" content=\"2020-06-14T20:20:56+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-08-17T13:51:49+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG7.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=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/\",\"url\":\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/\",\"name\":\"Dynamic Removal of Rows in Power Query - Bi Unija\",\"isPartOf\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG7.jpg\",\"datePublished\":\"2020-06-14T20:20:56+00:00\",\"dateModified\":\"2020-08-17T13:51:49+00:00\",\"author\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10\"},\"breadcrumb\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/#primaryimage\",\"url\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG7.jpg\",\"contentUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG7.jpg\",\"width\":720,\"height\":480},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/bi.unija.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dynamic Removal of Rows 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 Removal of Rows 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-removal-of-rows-in-power-query\/","og_locale":"en_US","og_type":"article","og_title":"Dynamic Removal of Rows in Power Query - Bi Unija","og_description":"In this article we\u2019ll see how to remove appropriate number of irrelevant rows in Power Query editor. We often work with data that starts with description like time of creation, location, author, etc. This metadata usually takes up first few","og_url":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/","og_site_name":"Bi Unija","article_published_time":"2020-06-14T20:20:56+00:00","article_modified_time":"2020-08-17T13:51:49+00:00","og_image":[{"width":720,"height":480,"url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG7.jpg","type":"image\/jpeg"}],"author":"Branka Trifunovi\u0107","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Branka Trifunovi\u0107","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/","url":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/","name":"Dynamic Removal of Rows in Power Query - Bi Unija","isPartOf":{"@id":"https:\/\/bi.unija.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/#primaryimage"},"image":{"@id":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/#primaryimage"},"thumbnailUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG7.jpg","datePublished":"2020-06-14T20:20:56+00:00","dateModified":"2020-08-17T13:51:49+00:00","author":{"@id":"https:\/\/bi.unija.com\/en\/#\/schema\/person\/3bed36623727c7162e421c4366a54e10"},"breadcrumb":{"@id":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/#primaryimage","url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG7.jpg","contentUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG7.jpg","width":720,"height":480},{"@type":"BreadcrumbList","@id":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/bi.unija.com\/en\/"},{"@type":"ListItem","position":2,"name":"Dynamic Removal of Rows 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\/1255"}],"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=1255"}],"version-history":[{"count":2,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1255\/revisions"}],"predecessor-version":[{"id":1381,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1255\/revisions\/1381"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media\/1205"}],"wp:attachment":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media?parent=1255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/categories?post=1255"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/tags?post=1255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}