{"id":1263,"date":"2020-06-14T20:22:04","date_gmt":"2020-06-14T20:22:04","guid":{"rendered":"http:\/\/bi.unija.com\/en\/?p=1263"},"modified":"2020-08-17T13:52:03","modified_gmt":"2020-08-17T13:52:03","slug":"dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder","status":"publish","type":"post","link":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/","title":{"rendered":"Dynamic Removal of Rows in Power Query for All the Files in a Folder"},"content":{"rendered":"

In this article we\u2019ll remove unnecessary rows for all the files inside a folder and merge them in one single table.<\/em><\/p>\n

Data files often contain metadata like time of creation, location, author, etc. Metadata usually takes up the first few rows of data. In Dynamic Removal of Rows in Power Query<\/a> we showed how to create a query that detects and removes the unwanted rows. Now we can use the query we made and change it into a function. We\u2019ll call this function on all the files in our folder. This is the way to clean and prepare data when we have a lot of similar files on hand.<\/p>\n

Function that removes the unwanted rows<\/h1>\n

We\u2019ll start with a query from the previous article. Query detects the number of unnecessary rows and removes them. Our query is called titanic. It removes redundant upper rows and returns a clean data table.<\/p>\n

<\/p>\n

We\u2019ll transform the query into a function. We first duplicate the query by right-clicking it and selecting Duplicate<\/strong>. We select the duplicate and open the Advanced Editor<\/strong>. Here we have all the steps of the query written in M language. We change the query into a function: we add the symbol ()=> <\/strong>to the start of the query. We add parameters inside the brackets. In our case we add parameter (FilePath), that stands for path of the input file to be cleaned. The function therefore starts with (FilePath)<\/strong> =>. We also change the actual file path in the function Source with the parameter FilePath, as seen on example below.<\/p>\n

<\/p>\n

We confirm with Done<\/strong>. Query is now written in italic, meaning it\u2019s now a function.<\/p>\n

<\/p>\n

We rename the function to fnTXT<\/em>. We can now enter filepath of any file we\u2019d like to edit. Invoke<\/strong> will call the function and edit the file. We\u2019ll get an edited table without the redundant rows. Since we don\u2019t want to do this for each file manually, we\u2019ll create a list of all the files in a folder and invoke the function on a list.<\/p>\n

List of all the files inside a folder<\/h1>\n

We create a list of all the files inside a folder by selecting New Source ><\/strong> Folder <\/strong>and finding a directory path. We confirm with Ok<\/strong> and then select Transform Data<\/strong>. We get a table that describes one file in each row.<\/p>\n

<\/p>\n

We\u2019re only interested in columns containing file path and file name. We select the columns in this order, first Folder Path and then Name. We right-click and select Remove Other Columns<\/strong>. We\u2019re left with two columns. We merge them by selecting Transform<\/strong> > Merge Columns<\/strong>. We leave the separator set to None and enter a new column name, for example Merged. We now have a column of all the file paths in a directory.<\/p>\n

<\/p>\n

We can now invoke the function on this column. We select Add Column<\/strong> > Invoke Custom Function<\/strong>. We enter the name of the new column, we select the function fnTXT under Function Query and confirm with Ok<\/strong>. We get a new column that contains all the edited tables.<\/p>\n

<\/p>\n

We can now merge all the tables: we click the Expand icon in fnTXT column and select the option Expand<\/strong>. We select all the tables and confirm with Ok<\/strong>.<\/p>\n

<\/p>\n

We get one big merged table.<\/p>\n

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

In this article we\u2019ll remove unnecessary rows for all the files inside a folder and merge them in one single table. Data files often contain metadata like time of creation, location, author, etc. Metadata usually takes up the first few<\/p>\n","protected":false},"author":5,"featured_media":1206,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[3],"tags":[9],"class_list":["post-1263","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 for All the Files in a Folder - 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-for-all-the-files-in-a-folder\/\" \/>\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 for All the Files in a Folder - Bi Unija\" \/>\n<meta property=\"og:description\" content=\"In this article we\u2019ll remove unnecessary rows for all the files inside a folder and merge them in one single table. Data files often contain metadata like time of creation, location, author, etc. Metadata usually takes up the first few\" \/>\n<meta property=\"og:url\" content=\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/\" \/>\n<meta property=\"og:site_name\" content=\"Bi Unija\" \/>\n<meta property=\"article:published_time\" content=\"2020-06-14T20:22:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-08-17T13:52:03+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG8.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=\"3 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-for-all-the-files-in-a-folder\/\",\"url\":\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/\",\"name\":\"Dynamic Removal of Rows in Power Query for All the Files in a Folder - Bi Unija\",\"isPartOf\":{\"@id\":\"https:\/\/bi.unija.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG8.jpg\",\"datePublished\":\"2020-06-14T20:22:04+00:00\",\"dateModified\":\"2020-08-17T13:52:03+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-for-all-the-files-in-a-folder\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/#primaryimage\",\"url\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG8.jpg\",\"contentUrl\":\"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG8.jpg\",\"width\":720,\"height\":480},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/#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 for All the Files in a Folder\"}]},{\"@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 for All the Files in a Folder - 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-for-all-the-files-in-a-folder\/","og_locale":"en_US","og_type":"article","og_title":"Dynamic Removal of Rows in Power Query for All the Files in a Folder - Bi Unija","og_description":"In this article we\u2019ll remove unnecessary rows for all the files inside a folder and merge them in one single table. Data files often contain metadata like time of creation, location, author, etc. Metadata usually takes up the first few","og_url":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/","og_site_name":"Bi Unija","article_published_time":"2020-06-14T20:22:04+00:00","article_modified_time":"2020-08-17T13:52:03+00:00","og_image":[{"width":720,"height":480,"url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG8.jpg","type":"image\/jpeg"}],"author":"Branka Trifunovi\u0107","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Branka Trifunovi\u0107","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/","url":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/","name":"Dynamic Removal of Rows in Power Query for All the Files in a Folder - Bi Unija","isPartOf":{"@id":"https:\/\/bi.unija.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/#primaryimage"},"image":{"@id":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/#primaryimage"},"thumbnailUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG8.jpg","datePublished":"2020-06-14T20:22:04+00:00","dateModified":"2020-08-17T13:52:03+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-for-all-the-files-in-a-folder\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/#primaryimage","url":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG8.jpg","contentUrl":"https:\/\/bi.unija.com\/en\/wp-content\/uploads\/sites\/2\/2020\/06\/ENG8.jpg","width":720,"height":480},{"@type":"BreadcrumbList","@id":"https:\/\/bi.unija.com\/en\/dynamic-removal-of-rows-in-power-query-for-all-the-files-in-a-folder\/#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 for All the Files in a Folder"}]},{"@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\/1263"}],"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=1263"}],"version-history":[{"count":3,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1263\/revisions"}],"predecessor-version":[{"id":1383,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/posts\/1263\/revisions\/1383"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media\/1206"}],"wp:attachment":[{"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/media?parent=1263"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/categories?post=1263"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bi.unija.com\/en\/wp-json\/wp\/v2\/tags?post=1263"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}