Forum Discussion

Stephane5959's avatar
Stephane5959
Regular Visitor
6 years ago
Solved

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 ...
Need Help
  • edhans's avatar
    edhans
    6 years ago

    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.

     

     

v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

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.