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.

     

     

Stephane5959's avatar
Stephane5959
Regular Visitor
6 years ago

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:

https://answers.microsoft.com/en-us/msoffice/forum/all/excel-power-pivot-does-not-correctly-interpret-tab/edc10cc0-f6af-4663-ab9b-f0741d13c892?rtAction=1597778574224

Now I hope they release an fix soon ....

Stephane

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    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.

    • Stephane5959's avatar
      Stephane5959
      Regular Visitor
      6 years ago

      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

      • edhans's avatar
        edhans
        Community Champion
        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.