Custom Delimiter when importing TXT file

[JoaoMS]

Dear all, i have a database of items of different Purchase Orders in TXT format. This database as expected grows everyday. Some of the items have "quotation marks" which give me problems when importing to Power BI (even to Excel). For example these are the items of one PO from the original ERP (Figure 1):

In this example, the positions 20 and 60 of the PO have a quote at the beginning of the Item Description and a double quote within the body of the text.

These quotes create problems when importing to Power BI. If I use the TAB delimiter, the initital quote and one of the double quotes of the positions 20 and 60 disappear, and the positions 30 and 70 are now missing (Figure 2): 

If I continue with Edit the data, the missing positions are combined with the previous items. For the example the information of the position 30 is now combined in the Position 20 Description (Figure 3):

What I found is if I edit the TXT file manually by deleting the first quote such:

> Original: "CLAVO AMARRE 4-1/2""(1000/CJ)-AISLAMIEN

> Updated: CLAVO AMARRE 4-1/2""(1000/CJ)-AISLAMIEN

I don't have anymore this problem (Figure 4):

Is there a way to customize the delimiter or the data within Power BI in order to solve this issue? 

I have uploaded the file in the following link:

https://1drv.ms/t/s!Av-7eDFmTF-QaqFR8XoCBerpIvY?e=kbYDho 

Thanks in advance

[Mariusz · Accepted solution]

Hi @JoaoMS 

You can click on the gear icon next to the Source step and adjust "Line Breaks" to "apply all lines breaks" 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Mariusz · Accepted solution]

Hi @JoaoMS 

You can click on the gear icon next to the Source step and adjust "Line Breaks" to "apply all lines breaks" 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[JoaoMS]

Hi @Mariusz , I know I have accepted the solution but I just figured out that although I have now all the positions of the items, not all the information has been properly placed. Do I need to create a new message in the forum?

Regards 

[Mariusz · Accepted solution]

Hi @JoaoMS 

Please see the attached file with transformations applied.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[JoaoMS]

Thank you! It works perfect. I didn't know about that feature.