Power query with txt for messages

[juan jimenez]

hi,

I would like to import the messages from conversations that I receive in txt format via Power Query. I have uploaded the "txt" here as a "docx" because this platform does not accept txt files!!.

Unfortunately, there are errors because sometimes the message lines are cut in 2 lines, and the cut part is considered an error.

1. Is there a way to concatenate the messages so that the cut phrases are not lost?

2. Could someone continue the code so that new files placed in the source folder are added to the spreadsheet without deleting the previous ones?

Thank you very much, Juan

[#2]

@juan jimenez 

1) Could you please clarify what is desired result for such blocks (if source is recognized correctly)

Where marked texts shall be or they shall be removed?

2) If you use From Folder connections files are appended, nothing is removed.

[#3]

hi Sergei,

1) The lines marked in red should go with the text from the immediately preceding line that starts with a date. They are part of the text of the message corresponding to that day and time.

Can this operation be done with Power Query? Can you help?

Thank you very much, juan

[#4]

@juan jimenez 

Okay, but it's better to know how above texts are separated in csv. Is that crlf, or lf or what. If you can't share .txt here perhaps you could share the source on OneDrive or like and share the link make it available for everyone.

[#5]

here is the link:

https://1drv.ms/f/s!AlPgnUYDBAuukhGiAS-qt_72zKCN?e=EB5eE8

thank you again

[#6]

@juan jimenez 

Please find query in attached file. in Source step change file path on your one. Result is

[#7]

Dear Sergei
Thank you very much for your proposal. Unfortunately, it didn't work when I added more lines to the text.
Could you tell me what went wrong?
This is the txt link
https://1drv.ms/t/s!AlPgnUYDBAuukhKK6qg6BPr41SqM?e=VRz89g
Juan

[#8 · Accepted solution]

@juan jimenez 

That didn't work since dates were converted using US locale. With UK locale it work, please see attached. You may change in Change Type with Locale step.

[#9]

@juan jimenez It looks like works since post is accepted. Good luck!

[#10]

Hello Sergei,

I apologize for insisting on this matter. I've tried importing these sentences, and I noticed that
1) not all the complete texts of the conversations appear, and
2) the order in which they were written has changed.

Could you please take a look at the attached PQ (https://1drv.ms/x/s!AlPgnUYDBAuukh9cF_02HLdlxqmr?e=fgrGGC)
and the txt in this link (https://1drv.ms/t/s!AlPgnUYDBAuukh7twVQBAt4qXE8L?e=KDCsjp)?




Thank you very much, Juan.

[#11]

@juan jimenez 

Hi Juan,

I see, that's since we have quotes within the text. Please check "Chat con XXX updated" query in attached.

On third step I used

= Table.AddColumn(FilteredFiles, "Data", each Table.FromColumns({Lines.FromBinary([Content], null, null, 65001)}))

instead of csv importing.

On intermediate steps where we split columns QuoteStyle.Csv was changed on QuoteStyle.None.

As for the sorting I kept initial index, on final step sorting is by date, time and Id.

Then you add another file please be sure it has exactly the same structure and applied data types.

[#12]

Dear Sergei,

your PQ was working perfectly until a new txt came and a problem arised. Would you mind telling me problem and even correcting the attached coding?
https://1drv.ms/f/s!AlPgnUYDBAuukjaxyOz8oGFm60vd?e=jehfft

[#13]

@juan jimenez 

Please check attached. It looks as