Refresh Power Query and Pivot Tables with SSIS

Refreshing Excel Power Query & Pivot Tables with SSIS and Task Factory

image 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.

image

And now I can schedule the refresh of any Power Query data connections or Pivot Tables with SSIS.

image

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!

12 thoughts on “Refreshing Excel Power Query & Pivot Tables with SSIS and Task Factory”

    1. 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.

  1. 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!

      1. 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

Comments are closed.