Power Query does not recognize tab as a delimiter in .txt files in the code

Question

this is my first post here, so I apologize in advance if the question has been already answered somewhere or I do something wrong. To summarize the problem:

I am doing some spectroscopy measurements and the data from the software I am using is saved in hundreds of .txt files. All files have the same content: first column refers to the wavelength, the second column is the intensity. Columns are separated from one another with a tab. The idea is to insert all of these .txt files in Power Query, rearrange the columns so there is only one column with the wavelength (since it is always the same for all measurements), and the remaining columns would be intensities (second column) of all inserted files.

Therefore, the desired output should look like this:

Wavelength (1st file), intensity (1st file), intensity (2nd file), intensity (3rd file),..., intensity (last file).

I found this brilliant solution, but the issue is that it works flawlessly if the columns are separated by a comma. I tried changing the code so it recognizes the tab, but stuff that I tried didn't work. I also found about Power Query yesterday, so I am a total beginner here. Here is the code:

let
    Source = Folder.Files("C:\Users\xxxxx\Desktop\new"),


// Standard UI; step renamed
FilteredTxt = Table.SelectRows(Source, each [Extension] = ".txt"),

// Standard UI; step renamed
RemovedColumns = Table.RemoveColumns(FilteredTxt,{"Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),

// UI add custom column "FileContents" with formula Csv.Document([Content]); step renamed
AddedFileContents = Table.AddColumn(RemovedColumns, "FileContents", each Csv.Document([Content])),

// Standard UI; step renamed
RemovedBinaryContent = Table.RemoveColumns(AddedFileContents,{"Content"}),

// In the next 3 steps, temporary names for the new columns are created ("Column2", "Column3", etcetera)
// Standard UI: add custom Index column, start at 2, increment 1
#"Added Index" = Table.AddIndexColumn(RemovedBinaryContent, "Index", 2, 1),

// Standard UI: select Index column, Transform tab, Format, Add Prefix: "Column"
#"Added Prefix" = Table.TransformColumns(#"Added Index", {{"Index", each "Column" & Text.From(_, "en-US"), type text}}), //type text

// Standard UI:
#"Renamed Columns" = Table.RenameColumns(#"Added Prefix",{{"Index", "ColumnName"}}),
// Now we have the names for the new columns

// Advanced Editor: create a list with records with FileContents (tables) and ColumnNames (text) (1 list item (or record) per txt file in the folder)
// From this list, the resulting table will be build in the next step.
ListOfRecords = Table.ToRecords(#"Renamed Columns"),

// Advanced Editor: use List.Accumulate to build the table with all columns, 
// starting with Column1 of the first file (Table.FromList(ListOfRecords{0}[FileContents][Column1], each {_}),)
// adding Column2 of each file for all items in ListOfRecords.
BuildTable = List.Accumulate(ListOfRecords,
                             Table.FromList(ListOfRecords{0}[FileContents][Column1], each{_}),
                             (TableSoFar,NewColumn) => 
                              Table.ExpandTableColumn(Table.NestedJoin(TableSoFar, "Column1", NewColumn[FileContents], "Column1", "Dummy", JoinKind.LeftOuter), "Dummy", {"Column2"}, {NewColumn[ColumnName]})),
    #"Sorted Rows" = Table.Sort(BuildTable,{{"Column1", Order.Ascending}})
in
    #"Sorted Rows"

    //each {_}
    //Splitter.SplitTextByWhitespace

This is the output I get when I run the code:

and if I change the first five of rows of .txt files so there is a comma between the columns, I get this:

The desired output (first five rows)

I was trying to change the each{_} in the Table.FromList line towards the end with the Splitter function, but it was not working.

I would be very grateful if you could take a look at the code, and suggest what needs to be changed in order for it to work.

Cheers!

horseyride · Accepted Answer · 2021-08-04T15:45:23.840

Modify your code to insert the #"Added Prefix2" code as below

#"Added Prefix" = Table.TransformColumns(#"Added Index", {{"Index", each "Column" & Text.From(_, "en-US"), type text}}), //type text
#"Added Prefix2" = Table.TransformColumns(#"Added Prefix" , {{"FileContents", each Table.SplitColumn(_, "Column1", Splitter.SplitTextByEachDelimiter({"#(tab)"}, QuoteStyle.Csv, false), {"Column1", "Column2"})}}),
// Standard UI:
#"Renamed Columns" = Table.RenameColumns(#"Added Prefix2",{{"Index", "ColumnName"}}),

I prefer this version when I do similar. More compact and preserves file names of source files

let Source = Folder.Files("C:\directory\subdirectory"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".txt")),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom", each Csv.Document(File.Contents([Folder Path]&"\"&[Name]),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Column1"}, {"Column1"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Column1", Splitter.SplitTextByEachDelimiter({"#(tab)"}, QuoteStyle.Csv, false), {"Column1", "Column2"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Split Column by Delimiter",{"Name", "Column1", "Column2"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns1", List.Distinct(#"Removed Other Columns1"[Name]), "Name", "Column2")
in  #"Pivoted Column"

Power Query does not recognize tab as a delimiter in .txt files in the code

1 Answers1