With SSIS 2014 and earlier there is currently not native way to refresh an Excel workbook which include Power Query queries. Now that functionality is rumored to be included with SQL Server 2016 but if you’re currently running SQL Server 2014 or 2012 you are out of luck. But that’s why Pragmatic Works put together the Excel Power Refresh component for SSIS.
Configure the Excel Power Refresh Task in SSIS
Configuring the Excel Power Refresh Task is pretty straightforward. There’s not a lot of complexity to this component, which is a good thing.
First create a Connection Manager to your Excel 2013 file that includes your Power Query queries. In my case I have an Excel workbook that has some Power Query queries that query NFL.com for some data that I used for a blog post on the new Excel 2016 chart types.
Once you create your connection to the Excel file, use the Data Connections and Pivot Table Sheets to select the queries and pivot table sheets that you wish to refresh.
And now I can schedule the refresh of any Power Query data connections or Pivot Tables with SSIS.
Very cool!
Resources
Download the free trial of Task Factory here.
Feedback?
If you have any questions or would like some more information on Task Factory, feel free to send me an email or leave a comment below. Thanks!
Any thoughts on how this compares to Power Update?
Hey Tim, I don’t have any practical experience with Power Update at this time but it appears to be a bit more mature compared to the Task Factory Power Query update component. I think they have different pros & cons. Power Update definitely has more capabilities regarding refreshing power queries and Power BI models and Task Factory Power Refresh is just one component included in the Task Factory toolbox of more than 45 different tasks and transformations, which are all leveraged through SSIS.
Thanks for the reply Dustin. It’s definitely nice to have options.
Thanks for reading, btw. Have a good weekend. 🙂
Hi Dustin – I just got finished watching your Power BI Desktop presentation from July 2015 over on Youtube. Great job bringing is novices into the loop on this exciting solution. At the end I found myself wanting more.
In particular, here’s what I don’t quite get: After you’ve published to …powerbi.com/groups/me…, how do you share this published report/dashboard with other users? Thanks in advance!
Thanks for watching, Scott. I’m glad you enjoyed the video. I need to put some content together on publishing, but for the basics, I suggest you check out this link: https://support.powerbi.com/knowledgebase/articles/461278-publish-from-power-bi-desktop
I hope that helps! Have a nice day!
Thanks for the quick reply. The PowerBI Desktop does indeed make publishing to the web easy. Sharing, however, remains a problem for beginners like me. I found this thread enlightening about my issues: http://community.powerbi.com/t5/Service/Publishing-from-desktop-application-to-group-workspace/m-p/1269#M318
Ah ok, sorry I misunderstood what you were asking. I think the best way to share published reports will be from the Power BI app. In the article you referenced it seems their issue is with sharing directly to a workspace. If you just need to share some reports/dashboards, you’ll need to publish first then share the report. Check out this link: https://support.powerbi.com/knowledgebase/articles/431008-share-and-unshare-a-dashboard-from-power-bi
Here’s what I found, please share with others…https://support.powerbi.com/knowledgebase/articles/431008-share-and-unshare-a-dashboard-from-power-bi.
I tested this and was able to successfully share a visualization I pinned to a dashboard.
Just posted that link this morning in reply. The Power BI knowledge base is a great resource.
Any guaranteed way of refreshing pivots in Excel 2010 via .Net? Came across this link and want to confirm that it’s true:
https://support.microsoft.com/en-us/kb/257757
Without knowing for sure, I would guess so but one product I’ve used before to refresh a PowerPivot workbook is Power Update. It worked well and there’s a free version, too: http://www.powerpivotpro.com/2015/02/introducing-power-update/