Importing data from text files into Excel using Power Query

Question

I'm new to Power Query in Excel. I have a folder full of text files, each text file has a single column of numbers like this:

0.000248559
0.000526185
0.000799283
0.000815998
0.000469385
0.00040233
0.0011139
0.000675324
0.0009126
0.00079092
0.00085176
0.00036504
0.00042588
0.00584473
0.0031418
0.00031418
0.00935102
0.00205123
0.0146039
6.37778e-005

I want to import all of these text files into Excel using Power Query. Each text file will occupy a column, with the column header being the file name of the text file from where it came.

So it should look something like this:

image

I'm not sure how to get Power Query to do this. When I try to import the folder containing all the text files and I bring up the Query Editor I transpose the data so that the file names are the first row, but underneath each file name is just says 'binary'. How do I get it to display the actual numbers from each text file?

horseyride horseyride · Accepted Answer · 2020-07-21T16:00:34

Below reads all files ending in *.txt into separate columns, with filename as column title

you can paste (and edit) in Home ... Advanced Editor...

//read all files in specified directory you fill in here
let Source = Folder.Files("C:\directory\subdirectory"),
//filter only txt files
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".txt")),
//Pull contents of each file into table with an index
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom", each Table.AddIndexColumn(Csv.Document(File.Contents([Folder Path]&"\"&[Name]),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),"Index")),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Column1", "Index"}, {"Column1", "Index"}),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"Column1", "Index", "Name"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Name]), "Name", "Column1"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in #"Removed Columns"

Importing data from text files into Excel using Power Query

1 Answers