seperating text to coloumn

Hi Anonymous ,

Please try the following :

1) While loading your text file into Power Query, choose delimiter as "Space" and not fixed width

2) Perform the following steps as given below. You can copy and paste the code in a blank query and replace <file_path> with the file path of your file.

let
    Source = Csv.Document(File.Contents(<file_path>),[Delimiter=" ", Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
    #"Merged Columns" = Table.CombineColumns(Source,{"Column2", "Column3", "Column4", "Column5"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Inserted First Characters" = Table.AddColumn(#"Merged Columns", "First Characters", each Text.Start([Column6], 9), type text),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Inserted First Characters", "Text Between Delimiters", each Text.BetweenDelimiters([Column6], ".", ".", 4, 0), type text),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Inserted Text Between Delimiters", "First Characters", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"First Characters.1", "First Characters.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Split Column by Delimiter",{"Column1", "Merged", "First Characters.1", "First Characters.2", "Text Between Delimiters", "Column6", "Column7", "Column8", "Column9"})
in
    #"Reordered Columns"

This will give you the desired output

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂