[Solved] Merge multiple txt files in Excel power query [closed]

by


Final try on this one

Use Home…advanced editor… and paste this function into PowerQuery. Name it: fnReadFile

(zFile)=>
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 ">>>"
CaratFind = Table.FindText(#"Added Index" , ">>>"){0}[Index],
FilterDate=Table.SelectRows(#"Added Index", each [Index] > CaratFind),
#"Added Custom" = Table.AddColumn(FilterDate, "Date", each DateFind),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}})
in #"Changed Type"

Use Home…advanced editor… 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

let Source = Folder.Files("C:\directory\subdirectory"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".txt")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each fnReadFile([Folder Path]&"\"&[Name])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1.1", "Column1.2", "Date"}, {"Column1.1", "Column1.2", "Date"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Column1.1", "Column1.2", "Date"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Date]), "Date", "Column1.2")
in  #"Pivoted Column"

2

solved Merge multiple txt files in Excel power query [closed]