Can you merge delimiters when importing a delimiter separated file

[Matjo]

I have a tab seperated file that is huge but during the export there has been a number of errors where extra tabs have 

been added as delimiters. Is there some setting that allows you to merge delimiters to handle cases where one row might have two

tabs? 

Cheers,
Mattias

[MarcelBeug]

There is no standard function but you can use the following function in Power Query, that will remove all leading, trailing and duplicate TrimStrings from a String:

(String as text, TrimString as text) as text => Text.Combine(List.Select(Text.Split(String,TrimString), each _ <> ""),TrimString)

Example: if you call the function TextLTMTrim, then

= TextLTMTrim("aabaacaadaaeaaa", "a")

returns:

bacadae

In order to remove leading, trailing and duplicate tabs, you can enter "#(tab)" as 2nd parameter.

[Matjo]

Ok, that is exactly what I need but I do not fully understand how I add this function to my query. 
Where do I define the function?
How do I apply the function to the rows of the file that I am importing?

Much appreciated, 
Mattias

[MarcelBeug · Accepted solution]

OK, you're right. That may not be too obvious, so I'll explain in this video:

Code created during video recording:

let
    Source = Csv.Document(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\CSV files with duplicate tabs.txt"),1,{0},null,1252),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{},each TextLTMTrim(_, "#(tab)")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Column1", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Name", type text}, {"Other Data", type text}})
in
    #"Changed Type2"

[Matjo]

Thanks a lot! 

I really appreciate the time you put in to helping me. 

Best regards,
Mattias