Consolidate column data|Power Query|Excel Forum|My Online Training Hub

Hello Mynda,

Thanks a lot for your answer.

Yes, the final result i'm talking about is what happens into Power Query.

Now....I did a modification on the way the file is exported from the SharePoint list (this is done by a workflow in Power Automate). what i do is :

I collect the information from my list into a collection (in a powerapps form), I assign this collection to a variable, I create the CSV file (using Power Automate) using this variable content (and at this stage everything is still formated the right way. 

However, I added a susbstitution formula on the "comment" when building my variable so it subsitutes the carriage return (Char(10)) with a space. that solves the issue on the CSV file. so now i get :

Department   Process   Comment

Dpt1              Proc1      This is my first comment

Dpt2              Proc2       The weather is beautiful

Dept3             Proc3       123

then I have a xlsx document, where i use Power Query to consolidate the content of all my CSV files (since the user can update the content of the list and reload the document (meaning it will overwrite the content of the CSVs) using Power Query in a general file allows for the data to be automatically updated).

the change i did with the substitution changed the result in Power Query to  : 

Department   Process   Comment

Dpt1              Proc1      This is my first comment

Dpt2              Proc2       The weather

 is Beautiful

Dept3             Proc3       123

But i can understand what i missing for this to work properly , it seems there is something additional that cuts the sentence into 2 pieces but i can't find anything cause my CSV is correct. I tried to add a subsitution (found somewhere that i could add Char(13)) on the subsitution but it doesn't change anything.

Unfortunately, this file is in my work environment which is restricted so i can't get a sample out of it 🙁

Thanks a lot for your time,

Cedric 

Hello Mynda,

yes, there must be something. In addition, the comment doesn't stay in the same column on a new line (which would be ok) it goes on the next line in the first column...that's what i don't understand.

I can't provide a sample because everything is locked here, i can't send anything out.

But i'm seeing something i didnt pay attention to ... the comment that i see in my CSV is the right one but in Power Query it's still loads the very old one from the beginning and i can't refresh it.

when i hit refresh, it make synchronization errors saying i don't have access to the resource. so i created a new file but the issue is still the same. I'm site owner on sharepoint i have full control so there should be somehting happening on the Excel side.

I deleted all the connection file i could find but it doesn't seem to help.

A dummy CSV file, i can create but it loads again the old version of my file so that doesn't really help  🙁

Cedric