One of the things I like least about Power Query at the moment is the lack of control over where the results of your queries end up in Excel. For example, if you create a new query and load it onto a worksheet, your table always ends up in the top left hand corner of a worksheet. Furthermore, it seems like every time you refresh your query, then the table it’s bound to gets deleted and recreated so any extra calculated columns or formatting you have added to the table disappear.
There is, however, a way around this. If you go to the Data tab in Excel and click the Existing Connections button you can see the OLEDB connection that Power Query creates in the “Connections in this Workbook” section:
You can then double-click on the connection and create a new table from that connection, call it whatever you want, add formatting, calculated columns and so on and all of these things will remain intact when you refresh. Even better, when you refresh the table it will re-execute the underlying Power Query query. This is a much better experience for consuming the results of your Power Query query, in my opinion.
Incidentally, Miguel Llopis of the Power Query team told me on the forum that this is an area of functionality that they hope to address in early 2014, so hopefully this blog post will be redundant in a few months.
Hello Chris:
I am working on on-premise data access using Power Query on Excel. I am able to pull data using Data Management Gateway on Excel and then uploaded the Excel on office 365 site. The problem is I am not able to refresh the data on Office 365 portal. Is this a known issue? Any work around for this?
Thanks,
Saurabh
When you say “Office 365 site”, do you mean a regular Office 365 site or a Power BI site? Have you read this forums thread? http://social.technet.microsoft.com/Forums/en-US/dcc9e668-30f8-4458-b123-0203dec874e1/onpremises-data-refresh?forum=powerbiforoffice365
Thanks for the quick reply Chris. I am trying o Power BI site only and trying to create oData feed for a on-premise data. I read the blog and response from John as the feature is only available with anonymous oData feeds, and Project Online data feeds. When can we expect oData feed for on-premise data with Microsoft Online Service Id authentication?
Thanks,
Saurabh
I have no idea, sorry.
Thanks for your tip. It has indeed allowed me to keep a calculated column. However, though it initially seems to work, after some time, the table loses its custom formats upon refresh. I have a date column which I set to the date format in power query. But no matter what I try, it ends up being displayed as integers. But at least my calculated column stays. (Using Excel 2010 & PQ Version: 2.9.3502.261)
hello Bertrand… I am running into this exact same issue. Did you find what was wrong? Please let us know if you found the solution. I will go back to search on this and probably pull my hair off shortly…
Sachin, this problem should be fixed in the latest version of Power Query. Have you got that installed?
Hi Chris,
another question regarding PowerQuery-Connections and PowerPivot-Usage of the data:
I have created a nice PPivot-Table based on a PQ with a .txt-file with KPIs and some measures underneath. THEN I gave the file to my collegue, who tried to change the underlying sourcefile. He ended up with a destroyed PPivot-Table and a message like OLE-DB Error, blablalba, try to deactivate and reactivate the connection. I’ve changed the PQ to load its data to an Excel-Sheet, used that sheet in the PP. Now the guy has to update two parts of the file: the PQ to load the data to the Excel-sheet and the PPivot-table to reflect the changes in the Excel-sheet.
Is there a better way to change the name of the sourcefile and still beeing able to load data without destroying the PPivot-model?
Best Regards
Patrick
Sounds strange – did your colleague have the same versions of Excel/Power Pivot/Power Query installed? How did he try to change the source file? Did he get the error in Power Pivot or Power Query?
I have a very similar issue in that I move my data source (an Excel workbook) to another folder and repointed the Power Query to the new location. It imported the data but could not add the data source to the data model and consequently I had to rebuild the model, which included recreating the forumulae … so frustrating :(. I wondered if I was doing it incorrectly i.e. I changed the initial Power Query’s “location”. Does anyone have any ideas about this?
That shouldn’t happen – you should definitely be able to point your Power Query query to a new data source without breaking anything.
Hi Chris,
I have created a PowerQuery connection to a PostGresSql database and now I promoted the powerpivot to a tabular Cube. Now the powerquery connection is added as a PushedDatasource. I have 2 questions you hopefully can answer:
1. Is there a way to reuse the powerquery connection?
2. An other option would be connection via ssas – odbc to postgressql but that is not working for me. Any advice in that area?
There’s no way, at the moment, to use Power Query to move data into SSAS Tabular. It’s something that a lot of people have asked for though so I hope it comes soon. I’m afraid I have never used PostGres with SSAS so I don’t know why the direct connection doesn’t work; I guess it’s because the SQL that SSAS generates isn’t compatible. The first thing to try would be to see whether you could create a linked server in SQL Server and connect through that.