excelpowerquerydata-import

Excel Power Query only imports column titles, not data


I am trying to use Power Query in Excel 2013 to import a folder full of 121 text files. Each text file has a column of numbers:

24
2.0000E+07
1.0000E+07
5.0000E+06
2.0000E+06
1.0000E+06
1.0000E+05
1.0000E+04
1.0000E+03
1.0000E+02
1.0000E+01
1.0000E+00
6.2500E-01
5.0000E-01
4.0000E-01
3.0000E-01
2.0000E-01
1.0000E-01
8.0000E-02
6.0000E-02
4.0000E-02
3.0000E-02
2.0000E-02
1.0000E-02
2.0000E-04
1.0000E-05
1.0516E-05
9.3907E-06
3.3497E-04
1.8445E-03
1.3411E-03
5.4756E-03
9.4254E-03
1.2390E-02
1.4350E-02
1.5677E-02
1.7293E-02
4.0507E-03
2.0602E-03
2.1823E-03
3.1392E-03
7.5455E-03
9.1609E-02
7.5750E-02
1.2536E-01
1.9400E-01
1.2207E-01
1.2811E-01
1.1341E-01
5.2564E-02
56
2.0000E+07
6.4300E+06
4.3000E+06
3.0000E+06
1.8500E+06
1.5000E+06
1.2000E+06
8.6100E+05
7.5000E+05
6.0000E+05
4.7000E+05
3.3000E+05
2.7000E+05
2.0000E+05
5.0000E+04
2.0000E+04
1.7000E+04
3.7400E+03
2.2500E+03
1.9200E+02
1.8800E+02
1.1800E+02
1.1600E+02
1.0500E+02
1.0100E+02
6.7500E+01
6.5000E+01
3.7100E+01
3.6000E+01
2.1800E+01
2.1200E+01
2.0500E+01
7.0000E+00
6.8800E+00
6.5000E+00
6.2500E+00
5.0000E+00
1.1300E+00
1.0800E+00
1.0100E+00
6.2500E-01
4.5000E-01
3.7500E-01
3.5000E-01
3.2500E-01
2.5000E-01
2.0000E-01
1.5000E-01
1.0000E-01
8.0000E-02
6.0000E-02
5.0000E-02
4.0000E-02
2.5300E-02
1.0000E-02
4.0000E-03
1.0000E-05

I want to use Power Query to import the entire folder into Excel, with the data in each text file having its own column, and the column header being the name of the text file.

Like this

The problem is that Power Query only seems to import the file names, but not the data within them.

So I get something like:

this

With no data underneath its respective column. What am I doing wrong? Would it have something to do with Power Query seeing the data as 'binary' instead of 'text'?


Solution

  • This should do what you want ... read in all .txt files in a directory, and then place the values from each into its own column where the column headers is the filename.

    Obviously, change the path in the first step

    Assumes a single column of data in each source file

    let Source = Folder.Files("C:\directory\subdirectory\"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".txt")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Table.AddIndexColumn(Csv.Document(File.Contents([Folder Path]&"\"&[Name]),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),"Index",1)),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Index"}, {"Column1", "Index"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom.1",{"Name", "Column1", "Index"}),
    #"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"