Forum Discussion
column separator stop to work
Yes, but you can disable that. In fact, I recommend you disable all automatic data loading in Excel. I have my Excel Power Query options set as follows:
Then, if you right-click on the query once you are actually in Excel and select the Load To menu option, you get this dialog box. Make sure Connection Only, and Load to Data Model are selected. Then only the data model will have the data, just as if you'd imported through Power Pivot. But now that is in Power Query, you can do all sorts of transformations before it loads. Filter, grouping, column renaming, adding new columns, removing columns, etc.
Stephane5959 it sounds like the delimiter changed. Can you go into Power Query and look at the step that is splitting the column and change the delimiter chosen to see if that fixes it? It will probably be "Split Column" and it will have a little gear next to it. You can change the splitter in this dialog box. Note there is a "custom" value too in the dropdown you can use to customize it.
To be more specific, you'd need to share a copy of the file (no priviate data, and just 2-3 rows would be sufficient) via OneDrive, Dropbox, etc. for us to take a look at it and see which column is causing the issue and what the ASCII character is that you should be splitting by.