Excel

Defining Configurable Settings for your Queries

Defining Configurable Settings for your Queries

(The information in this blog post is current as of the March 2014 release of Power Query.) The Problem A colleague sent me a copy of the Excel workbook they used for a blog post about using Power Query with Project Online (see the fantastic article: Creating burndown charts for Project using Power Pivot and … Continued

Skylanders Power View Workbook

Skylanders Power View Workbook

I had a lot of requests for the Skylanders BI Application I showed off at the BI Power Hour, so I’ve made it available on skydrive. For those that missed it, the “app” is an Excel workbook which uses Power View (on top of Power Pivot) that I created to help my kids made better … Continued

Error: The Microsoft Access database engine cannot open or write to the file

Error: The Microsoft Access database engine cannot open or write to the file

Issue – you run an SSIS package that uses an Excel Source or Excel Destination and get the following error: [Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0×80004005. An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0×80004005 Description: "The Microsoft … Continued

Refresh an Excel Workbook with a Script Task

Refresh an Excel Workbook with a Script Task

Are you loading data from an Excel workbook with external queries using SSIS? Do you want to make sure those queries are refreshed before the load takes place? Have I got the post for you! I recently had to do this myself, and after some googling with Bing, I found an MSDN forum post that … Continued

Using Data Explorer with Power View

Using Data Explorer with Power View

In my first Data Explorer post, I pulled a list of event data from the SQL Saturday website. I then showed how to do dynamic lookups against an address verification service to pull back location data about the event’s venue. In this post I’ll complete my experiment and plot out each location on a map … Continued

Dynamic Lookups With Data Explorer

Dynamic Lookups With Data Explorer

In my previous post, I used Data Explorer to grab the list of upcoming events from the SQL Saturday website. Excel has had the ability to grab data from HTML pages for awhile, but using Data Explorer provided a richer set of options, and allowed me to apply a series of transforms to get the … Continued

Exploring Data Explorer

Exploring Data Explorer

This week the Data Explorer team announced that a preview of their Excel add-in is now available! I’ve been meaning to try it out for a while, so I went ahead and downloaded the Office 2013 version then walked through some of the tutorials on the Office Help site. Once the add-in is installed, it … Continued