- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

column separator stop to work
Hi,
I have a power pivot report linked to text files. As i just refreshed the source, the colum separator refuse to work as usualy with Tab (t), i tried all options and it keeps showing all data in one column. Any idea what can cause the issue?
Thanks a lot
Stephane
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,
All thanks for the help,
It seems like I can use the tab separator with power query, only power pivot doesnt accept anymore since I install my last update. I could find from a separate microsoft support a similar case, it seems related to an update of excel:
Now I hope they release an fix soon ....
Stephane
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi @Stephane5959 - you should really import via Power Query, then load that to the data model. The only reason Power Pivot even has an import function is at one time it was a separate addin and was made available before Power Query was - this is nearly a decade ago.
Power Pivot imports do not allow any filtering, transformations, etc.
So yes, I hope the Excel Power Pivot bug is fixed, but the best practice is to always go through Power Query for 99.9% of your data sources. Things like SQL Server Analysis Services is a bit different.
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks for the advice,
If the data set is very large, then it creates a tab in my excel from using the import via power query, doesnt it make my excel file bigger?
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks a lot, this is helpful
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Great @Stephane5959 - if you could mark one of these posts as the solution if you are satisfied I'd appreciate it and it may help others searching for solutions, especially if they are bitten by this same Excel Power Pivot bug until it is fixed.
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi, @Stephane5959
I wonder what colum separator you are using. I created data to reproduce your scenario.
When you connect the text with text/csv connector, you need to choose which delimiter to use.
If you don't choose a corresponding delimiter, it will keep showing all data in one column.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@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.
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Try to create quick another powerpivot report with your text file, might be delimiter in the source file is changed, see if this works, or upload here the sample data.

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
03-26-2024 04:25 AM | |||
05-27-2024 09:07 AM | |||
09-13-2024 05:19 AM | |||
08-08-2024 10:34 PM | |||
Anonymous
| 03-13-2024 02:29 AM |