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.
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
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.
Thanks a lot, this is helpful
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.