{"id":23792,"date":"2022-11-28T10:27:20","date_gmt":"2022-11-28T04:57:20","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/"},"modified":"2022-11-28T10:27:20","modified_gmt":"2022-11-28T04:57:20","slug":"solved-merge-multiple-txt-files-in-excel-power-query-closed","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/","title":{"rendered":"[Solved] Merge multiple txt files in Excel power query [closed]"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-57992971\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"57992971\" data-parentid=\"57992149\" data-score=\"0\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>Final try on this one<\/p>\n<p>Use Home&#8230;advanced editor&#8230; and paste this function into PowerQuery.  Name it:   fnReadFile<\/p>\n<pre><code>(zFile)=&gt;\nlet Source = Csv.Document(File.Contents(zFile),[Delimiter=\"#(tab)\", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),\n#\"Added Index\" = Table.AddIndexColumn(Source, \"Index\", 0, 1),\n\/\/ Date row includes text \"Date: \"\nDateFind = Text.Replace(Table.FindText(#\"Added Index\" , \"Date\"){0}[Column1],\"Date: \",\"\"),\n\/\/ Row preceeding data includes text \"&gt;&gt;&gt;\"\nCaratFind = Table.FindText(#\"Added Index\" , \"&gt;&gt;&gt;\"){0}[Index],\nFilterDate=Table.SelectRows(#\"Added Index\", each [Index] &gt; CaratFind),\n#\"Added Custom\" = Table.AddColumn(FilterDate, \"Date\", each DateFind),\n#\"Removed Columns\" = Table.RemoveColumns(#\"Added Custom\",{\"Index\"}),\n#\"Split Column by Delimiter\" = Table.SplitColumn(#\"Removed Columns\", \"Column1\", Splitter.SplitTextByEachDelimiter({\" \"}, QuoteStyle.Csv, false), {\"Column1.1\", \"Column1.2\"}),\n#\"Changed Type\" = Table.TransformColumnTypes(#\"Split Column by Delimiter\",{{\"Column1.1\", type text}, {\"Column1.2\", Int64.Type}})\nin #\"Changed Type\"\n<\/code><\/pre>\n<p>Use Home&#8230;advanced editor&#8230; and paste this query into PowerQuery after changing the first row to your appropriate directory.  It will read in all .txt files and combine them as desired based on function above<\/p>\n<pre><code>let Source = Folder.Files(\"C:\\directory\\subdirectory\"),\n#\"Filtered Rows\" = Table.SelectRows(Source, each ([Extension] = \".txt\")),\n#\"Added Custom\" = Table.AddColumn(#\"Filtered Rows\", \"Custom\", each fnReadFile([Folder Path]&amp;\"\\\"&amp;[Name])),\n#\"Expanded Custom\" = Table.ExpandTableColumn(#\"Added Custom\", \"Custom\", {\"Column1.1\", \"Column1.2\", \"Date\"}, {\"Column1.1\", \"Column1.2\", \"Date\"}),\n#\"Removed Other Columns\" = Table.SelectColumns(#\"Expanded Custom\",{\"Column1.1\", \"Column1.2\", \"Date\"}),\n#\"Pivoted Column\" = Table.Pivot(#\"Removed Other Columns\", List.Distinct(#\"Removed Other Columns\"[Date]), \"Date\", \"Column1.2\")\nin  #\"Pivoted Column\"\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">2<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Merge multiple txt files in Excel power query [closed] <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Final try on this one Use Home&#8230;advanced editor&#8230; and paste this function into PowerQuery. Name it: fnReadFile (zFile)=&gt; let Source = Csv.Document(File.Contents(zFile),[Delimiter=&#8221;#(tab)&#8221;, Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]), #&#8221;Added Index&#8221; = Table.AddIndexColumn(Source, &#8220;Index&#8221;, 0, 1), \/\/ Date row includes text &#8220;Date: &#8221; DateFind = Text.Replace(Table.FindText(#&#8221;Added Index&#8221; , &#8220;Date&#8221;){0}[Column1],&#8221;Date: &#8220;,&#8221;&#8221;), \/\/ Row preceeding data includes text &#8220;&gt;&gt;&gt;&#8221; CaratFind &#8230; <a title=\"[Solved] Merge multiple txt files in Excel power query [closed]\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/\" aria-label=\"More on [Solved] Merge multiple txt files in Excel power query [closed]\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[400,3548],"class_list":["post-23792","post","type-post","status-publish","format-standard","hentry","category-solved","tag-excel","tag-powerquery"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] Merge multiple txt files in Excel power query [closed] - JassWeb<\/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:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Merge multiple txt files in Excel power query [closed] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Final try on this one Use Home&#8230;advanced editor&#8230; and paste this function into PowerQuery. Name it: fnReadFile (zFile)=&gt; let Source = Csv.Document(File.Contents(zFile),[Delimiter=&quot;#(tab)&quot;, Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]), #&quot;Added Index&quot; = Table.AddIndexColumn(Source, &quot;Index&quot;, 0, 1), \/\/ Date row includes text &quot;Date: &quot; DateFind = Text.Replace(Table.FindText(#&quot;Added Index&quot; , &quot;Date&quot;){0}[Column1],&quot;Date: &quot;,&quot;&quot;), \/\/ Row preceeding data includes text &quot;&gt;&gt;&gt;&quot; CaratFind ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-11-28T04:57:20+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\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\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Merge multiple txt files in Excel power query [closed]\",\"datePublished\":\"2022-11-28T04:57:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/\"},\"wordCount\":75,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"excel\",\"powerquery\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/\",\"name\":\"[Solved] Merge multiple txt files in Excel power query [closed] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-11-28T04:57:20+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Merge multiple txt files in Excel power query [closed]\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Merge multiple txt files in Excel power query [closed] - JassWeb","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:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Merge multiple txt files in Excel power query [closed] - JassWeb","og_description":"[ad_1] Final try on this one Use Home&#8230;advanced editor&#8230; and paste this function into PowerQuery. Name it: fnReadFile (zFile)=&gt; let Source = Csv.Document(File.Contents(zFile),[Delimiter=\"#(tab)\", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]), #\"Added Index\" = Table.AddIndexColumn(Source, \"Index\", 0, 1), \/\/ Date row includes text \"Date: \" DateFind = Text.Replace(Table.FindText(#\"Added Index\" , \"Date\"){0}[Column1],\"Date: \",\"\"), \/\/ Row preceeding data includes text \"&gt;&gt;&gt;\" CaratFind ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/","og_site_name":"JassWeb","article_published_time":"2022-11-28T04:57:20+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Merge multiple txt files in Excel power query [closed]","datePublished":"2022-11-28T04:57:20+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/"},"wordCount":75,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["excel","powerquery"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/","url":"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/","name":"[Solved] Merge multiple txt files in Excel power query [closed] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-11-28T04:57:20+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-merge-multiple-txt-files-in-excel-power-query-closed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Merge multiple txt files in Excel power query [closed]"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/23792","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=23792"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/23792\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=23792"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=23792"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=23792"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}