Query data from unstructured text file (*.txt)

[DAXRichArd]

Hello,

As usual I searched the forum first as well as youtube vids before posting a request for help.

Notes:

• Using Power BI Report Server (PBI RS).
• Unstructured text file with 8 columns.
• The columns are separated by spaces but the number of spaces between the columns vary from column to column.

I have an unstructured text file. When I query the text file, PBI RS puts all the data into one column. I can't figure out how to separate the data into the appropriate columns.

I query the text file.

Go to Get Data > Text/CSV (import data from text or CSV file).

Select the file.

The file origin window opens and displays a sample of the contents. All the data is in one column.

The file origin window only provides me the options of LOAD or EDIT.

The EDIT button does not have any options.

I choose EDIT and the file is loaded into Query Editor.

I tried using the SPLIT COLUMN but there aren't any features where I can input 'fixed width' for the space delimintor (some youtube tutorials have shown this option, but my guess is it is not available for PBI RS).

I've hacked at Power Query M functions but... I don't know what I'm doing.

Your help is appreciated.

Rich Ard

I can provide a sample file, but I didn't see an option here where I could load it into this post.

[edhans · Accepted solution]

You should have "Split Column by Positions" - this is a very old Split transformation and RS should have it.

If not, the Table.SplitColumn is surely available even if not via the UI. 

= Table.SplitColumn(#"Filtered Rows", "Image", Splitter.SplitTextByPositions({4, 9, 20}), {"Image.1", "Image.2", "Image.3"})

That is splitting the table #"Filtered Rows" (the previous step) column [Image] into 3 new columns (Image.1/2/3) at postions 4, 9, and 20. 

With that info you can manually create your step if that menu option isn't there.

[Anonymous · Accepted solution]

Hi @DAXRichArd 

Try selecting the column first. In the PBI RS the ribbon changes quite a bit depending on the context.

Kind regards,

JB

[DAXRichArd]

Thx Jborro.  I'll look again. I recall 'right-click-selecting' the column headers to see what options were there. Maybe I overlooked something. I'll pay attention again to the ribbon.

Big thanks and have a great week!

Rich Ard

[edhans · Accepted solution]

You should have "Split Column by Positions" - this is a very old Split transformation and RS should have it.

If not, the Table.SplitColumn is surely available even if not via the UI. 

= Table.SplitColumn(#"Filtered Rows", "Image", Splitter.SplitTextByPositions({4, 9, 20}), {"Image.1", "Image.2", "Image.3"})

That is splitting the table #"Filtered Rows" (the previous step) column [Image] into 3 new columns (Image.1/2/3) at postions 4, 9, and 20. 

With that info you can manually create your step if that menu option isn't there.

[DAXRichArd]

Thx edhans! I was able to figure it out prior to receiving your reply. First a big thank you.

The split column by position option is not avialable in PBI Report Server. What I did do though is I split the column by number of characters. Then I hacked at the M-Query code to expand it. In essence I replicated the by position option but through the M-Query code. I don't know M-Query so I HACKED! HACKED! HACKED! HACKED! until I got it.

Again big thank you for your time and response!

Rich Ard