Forum Discussion
seperating text to coloumn
I have data in below format .txt file and the file is in fixed width
10010101 Treasury Account - N 1101.0000.000.10010101.000.784.0000.0000 (3,366,650,063.01) (6,328,498.00) (3,372,978,561.01)
10010101 Treasury Account - N 1101.0000.000.10010101.000.840.0000.0000 (7,268,587.56) 0.00 (7,268,587.56)
I need to connect power bi to the above text file and get the output as below in eight different coloumns
10010101 Treasury Account - N 1101 0000 784 (3,366,650,063.01) (6,328,498.00) (3,372,978,561.01)
Highlighted alternate coloumns in bold
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! 🙂
1 Reply
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! 🙂