DirectQuery in Power BI Desktop

In the latest Power BI Desktop a new Preview features was released that now allows you to connect using DirectQuery to either SQL Server or Azure SQL Databases.  DirectQuery is a really neat feature that allows you to point to the live version of the data source rather than importing the data into a data model in Power BI Desktop. 

Normally when you want to get an updated dataset in the Power BI Desktop you would have to manually click the refresh button (this can be automated in the Power BI Service), which would initiate a full reimport of your data.  This refresh could take a variable amount of time depending on how much data your have.  For instance, if you’re refreshing a very large table you may be waiting quite a while to see the newly added data. 

With DirectQuery data imports are not required because you’re always looking at a live version of the data.  Let me show you how it works!

Turning on the DirectQuery Preview

Now, because DirectQuery is still in Preview you must first activate the feature by navigating to File->Options and settings->Options->Preview Features then check DirectQuery for SQL Server and Azure SQL Database


Once you click OK you may be prompted to restart the Power BI Desktop to utilize the feature.

Using DirectQuery in Power BI Desktop

Next make a connection either to an On-Premises SQL Server or Azure SQL database.

Go to the Home ribbon and select Get Data then SQL Server.


Provide your Server and Database names then click OK. ***Do not use a SQL statement.  It is not currently supported with DirectQuery***


From the Navigator pane choose the table(s) you would like to use.  I’m just going to pick the DimProduct table for this example and then click Load.  You could select Edit and that would launch the Query Editor where you could manipulate the extract.  This would allow you to add any business rules needed to the data before visualizing it.


Next you will be prompted to select what you want to connect to the data. Again, Import means the data will physically be imported into the data model and DirectQuery means you will retain a live connection to the data source.  Select DirectQuery then click OK.


With the connection established you can start to visualize the data.  I’ve just brought some fields into a simple table.


How can you prove this is a live connection? Go update the data!

I made a quick change to the first product listed and then clicked the Refresh button on the Home ribbon. Normally this refresh button would attempt to reimport data into the model but because we’re using DirectQuery it just refreshed the visualizations to show the new data in the live data source.


This means that there is not data model being used, just a connection to the live data.  There are some implications of the data modeling not being utilized here.  If you look at the screenshot above you’ll see there is no Data view, only a Relationship View.  That’s because there is no model for the data to be stored in.  It also means you cannot create calculations because there is no model for them to be stored in.  There are a few other limitation:

  • All tables must come from a single database
  • If the Query Editor query is overly complex an error will occur. To remedy the error you must: delete the problematic step in Query Editor, or Import the data instead of using DirectQuery
  • You cannot enter a SQL query when connecting to data
  • You cannot add calculated measures, columns, or tables
  • Relationship filtering is limited to a single direction, rather than both directions
  • You cannot change the data type of a column

These and other details can be found at the Power BI Support Knowledgebase here.

I think this is a really cool feature and I can’t wait for it to be officially part of the product!

Power BI Desktop: A Guide for Excel Users

Today Microsoft officially released the new development tool for Power BI called Power BI Desktop.  First of all let me share my excitement in what this release means for Power BI.  The team at Microsoft has done an incredible job and the results are something they can truly be proud of.

This new release presents a lot of questions for those who have built Power BI solutions in the past using Excel though.  So I thought it would be appropriate in this post to answer many of those questions as you begin exploring Power BI Desktop and compare it to Excel capabilities.

**Disclaimer: Information current as of 7/24/2015**


The good news is fundamentally what you’re doing with Power BI isn’t changing. You’re first going to find the data you want to import.  Then you may apply some business rules or transformations to the data.  Next, you’ll organize or model the data, which is an especially important step if you’re pulling from multiple data sources so they all relate properly to each other.  Last, you take what you’ve modeled and visualize it in reports that are presented to your users.

So if these steps haven’t changed then what has?  Well, the main changes have come with which tool you may choose to do your development in but there is also significant changes from the visualization side that are important to highlight. Let’s look at each of these steps: Importing, Transforms, Modeling and Visualizing your data and discuss how things have changed as you begin to develop in the Power BI Desktop application.

Importing Data


When working in Excel if you wanted to import data you had 3 possible ways to do this. This could be done with the Data, Power Pivot, or Power Query tabs in Excel.  You would choose each of these for different reasons.  The Data tab would be used to connect often to more enterprise data sources like Analysis Services cubes or Tabular models that have been prepared by IT.  The Power Pivot tab would be used to import data and begin modeling it with calculations, hierarchies, KPIs, etc..  Then the Power Query tab would not only import data but also allow you to apply business rules to the data through a built-in transformation language.  Many people have questioned whether or not to start with Power Query or Power Pivot to import data in Excel.

My guidance to you now would be to get in the habit of starting with Power Query if you’re not already because it presents you with many advantages from an overall Power BI perspective.  Consider things like additional data source options, the ability to share queries, and the continuing upgrades coming with Power Query as just a few reasons.

I’ll also mention that in Excel 2016 you will find Power Query under the Data tab going forward.

Power BI Desktop

In the Power BI Desktop application this story becomes much more simple. The one and only way to import data starts with Power Query.  By selecting Get Data in Power BI Desktop you’re presented with a very large (yet continuing to grow) list of data sources that you can use for importing data.


This is using Power Query as initial tool to connect to the data you need.  After you select the data you want you can decide if you want to bring it in as-is by selecting Load or if you need to apply business rules to the data first before importing you can select Edit.


Behind the scenes what this means is when you select Load the data immediately gets placed into an in-memory data model (aka Power Pivot).  If you select Edit then it will launch the Query Editor (aka Power Query).  Here you can continue to modify the data using the user interface or by writing your own M Query.

The one thing I’ll mention that’s currently missing from the Power BI Desktop for importing data is it’s currently missing the Data Catalog Search that Excel Power Query has.  This allows you to search public and organization queries.

Transform Data


In Excel if you want to apply business rules or transform data you will likely be using Power Query.  It’s the only tool that really allows you to modify the results of data as you import it in.  Tools like Power Pivot are all about importing first, completely read only, and then building on top of what you’ve imported.  While Power Query has an in-depth query language for modifying data during the import process.  This also can use features like Query Folding to run the final query against the data source and not on the client side.

Power BI Desktop

Really no big changes here in regards to the actual transformation process.  The Query Editor for Power Query in Excel looks essentially the same as it does in the Power BI Desktop.


The one thing I’ll note here is that it will be difficult to accomplish things like macro integration and creating user interactive workbooks like I’ve show here.

Modeling Data


Data modeling in Excel is done with the in-memory technology called Power Pivot.  Power Pivot has been available since Excel 2010 as a great way to do things like create relationships between currently unrelated items, create hierarchies as a way to navigate deeper into your data, create KPIs to analyze metrics in more in-depth, and many other features.

Power Pivot is still the core of what makes visualization work and work fast regardless of whether it’s Excel or Power BI Desktop.

Power BI Desktop

In the Power BI Desktop there’s still a lot of growing to do as far as creating data models.  I’m not too concerned about this though because as I’ve seen Power BI Desktop grow most recent investment has been placed on the data modeling side.  For example, up until the most recent release there was no data or diagram view like you have in Excel but with the public release of Power BI Desktop today you will now find these two features added.


You’re also seeing some things released to the desktop application before Excel.  For example, there are new DAX capabilities in the Power BI Desktop that aren’t available until Excel 2016.  In Power BI Desktop you now have the ability to do things like variables in DAX and there are new functions also available.  Here’s an example of the new DATEDIFF function:


There are even some new more advanced relationship properties to help deal with many-to-many relationships and bidirectional relationships that are currently only available in the desktop application. Chris Webb has a great post detailing this update here.

There’s still a lot of work to do here still to have full feature parity between Excel and the Power BI Desktop application.  Again this is as of 7/24/2015 and I anticipate many of these will be added as time goes on but here a quick list of some features that are currently not available to Power BI Desktop that are in Excel:

  • Perspectives
  • Hierarchies
  • Web URL Image support only (No database images yet)
  • Default Field Set (assigned default fields to a table)
  • Table Behavior
  • Summarize By (for changing the default aggregation behavior)

Some of these are more critical than others but for the most part you should be able to do what you need in the Power BI Desktop application when it comes to modeling now.

Now even though many of these properties don’t exist in Power BI Desktop that doesn’t mean you can’t import an Excel workbook to that’s already turned on these features and use them with visualizations.

Visualizing Data


In Excel you have multiple options for how to visualize data once it’s imported.  You can do standard Excel PivotTables or PivotCharts, Apps for Office, Power View, or Power Map. Depending on what type of data you’re working with or what the desired final report needs to look like would determine which route you chose.  Geographical data would fit nicely into Power Map, Power View or even some Apps for Office visualizations.  Power View is great for exploring data and drilling deeper into your data.  They all have a purpose depending on what your needs are

Power BI Desktop

The Power BI Desktop uses exclusively Power View to visualize your data, but it’s a significantly overhauled version of Power View.  You’re now given new visualizations like:

  • Combo chart
  • Tree Map
  • Area Chart
  • Filled Map
  • Funnel Chart
  • Gauges
  • Donut Chart
  • Matrix

Here’s a few of the new visualizations


The interactive nature of Power View that you are used to in Excel still exists here and it is still dependent on a well defined model. So if you click on a bar in a bar chart the rest of the report will filter on that selection.

Not only are you seeing new amazing visualizations but it’s also using a framework that’s very extendable.  Meaning you can use custom visualizations that you create on your own or that someone else created.  Check out this Power BI Visual Playground on GitHub to see what’s possible.

You also have a quick and easy way to publish the results to your Power BI site with a simple click of the Publish button in the ribbon the results will be shared with others.


Now this has simply been just a comparison between what’s possible in the Power BI Desktop application versus Excel.  If you’re curious about the other Power BI features that were released as well you should read this blog from the Power BI team.

Excel Services in Power BI

**Information current as of 7/22/2015**

There’s lots of exciting news this week with Power BI so expect several short blog posts detailing each.  Here’s one you can try now that I was pretty excited to see.

So here’s the news.  You can now expose standard Excel workbooks as another asset in your reports library in Power BI.  By selecting your workbook a link is created that allows you to launch Excel Services visualizing the workbook.

This will allow you to visualize things like PivotTables, Charts, Slicers and Power View sheets in the same place as your Power BI Dashboards

Current Limitations

  • The workbook must be stored in OneDrive for Business, which of course not everyone has.
  • Doesn’t integrate with other Power BI visualizations.  I’d love to see this in the future.  PerformancePoint allowed visualizations from Excel Services and it’s own proprietary visualization to integrate together.  Would love to see that again here.  Currently it just launches Excel Services to the workbook

How it Works

  • If you haven’t already, you’ll need to sign up at
  • Once you’re signed up, you can sign into at to get started.
  • To get started  select Get Data.


  • Select that you want to import data from the Files option.


  • Select OneDrive – Business.  Again OneDrive for Business is the only way to currently do this.


  • Select the file you want to import and then click Connect.


  • Next you’ll choose: do you want your workbook to be a data source for a Power BI dashboard or do you want to bring your Excel workbook in as an Excel Services report.  This blog is all about demoing the Excel Services component.  Select Connect, Manage and View Excel in Power BI.


  • This will take a few moments to process but once it does you will be taken back to the dashboard view of Power BI and you will find the Excel Workbook listed as a new asset in the Report folder.


  • When you select this you’re given several options including: View, Edit, Rename, Schedule Refresh, Refresh Now, and Remove
  • If you click View or Edit it will launch your workbook in Excel Services.

Very cool!  I’m hoping for even more integration like this very soon.  For example, I know everyone would be really excited to see Reporting Services integrated so we could have one repository for all our reporting assets.

Creating a Date Dimension with Power Query

A date dimension or table can be extremely important when working on a Power BI project, or BI projects in general for that mater. Here’s some of the quick benefits and reasons why you need a date table:

  • Helpful when filtering data
    • Filter by year, quarter, month, etc…
  • Helpful for drilling into a hierarchy of dates
    • Expand 2015 to see all the months within that year
    • Drill in to January to see all the days within that month
  • It’s required to do Time Intelligence functions in Power Pivot with DAX
    • Calculating year to date, prior period, etc…
  • It allows you to track important dates like holidays in a central spot.
    • Theses can be applied to filters
  • Allows you to track multiple types of dates
    • Calendar, fiscal, manufacturing, etc…

Just about any project will likely require one or many of these features. So if you’re working on a Power BI project what are your options?

  1. Import a date table that exist in your source already and use it
  2. Import a date table from the Azure Marketplace (I’ve used DateStream in the past successfully)
  3. Use Power Query and the M query language to create your own date table from scratch

For this post I’d like to share with you how to do option 3 using a script that I’ve created to generate a date dimension. Here’s the steps to replicate this table on your own.

Creating the Date Dimension

  • Launch Excel
  • Go to Power Query tab. If you haven’t downloaded Power Query already you can do so here.
  • Select From Other Sources > Blank Query. This will launch the Power Query Editor .
  • Select Advanced Editor in either the Home or View tab of the editor.
  • Remove any code that the editor is currently story and replace it with the following:
//Create Date Dimension
(StartDate as date, EndDate as date)=>

    //Capture the date range from the parameters
    StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), 
    EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), 

    //Get the number of dates that will be required for the table
    GetDateCount = Duration.Days(EndDate - StartDate),

    //Take the count of dates and turn it into a list of dates
    GetDateList = List.Dates(StartDate, GetDateCount, 

    //Convert the list into a table
    DateListToTable = Table.FromList(GetDateList, 
    Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),

    //Create various date attributes from the date column
    //Add Year Column
    YearNumber = Table.AddColumn(DateListToTable, "Year", 
    each Date.Year([Date])),

    //Add Quarter Column
    QuarterNumber = Table.AddColumn(YearNumber , "Quarter", 
    each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),

    //Add Week Number Column
    WeekNumber= Table.AddColumn(QuarterNumber , "Week Number", 
    each Date.WeekOfYear([Date])),

    //Add Month Number Column
    MonthNumber = Table.AddColumn(WeekNumber, "Month Number", 
    each Date.Month([Date])),

    //Add Month Name Column
    MonthName = Table.AddColumn(MonthNumber , "Month", 
    each Date.ToText([Date],"MMMM")),

    //Add Day of Week Column
    DayOfWeek = Table.AddColumn(MonthName , "Day of Week", 
    each Date.ToText([Date],"dddd"))


You’ll find documentation for this script embedded in the code to help guide you through what each expression is doing.

  • Click OK. This query is actually a function that accepts parameters so you will see that it’s waiting for you to invoke it with values.


  • Click Invoke and provide the range of dates that you would like the date table to return back. Then click OK

The results can now be integrated into your solution.  For example, you may add this to an existing Power Pivot data model by selecting Close & Load To.

I’ve seen many date tables with dozens of columns and a variety of types of dates.  I left this one fairly simple so you can add your own customizations to it.  Any thoughts on date fields that may be universally needed by others?  Let me know and I’ll work on adding it to the script.

Here’s a few other takes on solving this problem with Power Query:

Creating a SQL 2016 CTP2 Virtual Machine in Azure

With the CTP2 of SQL Server 2016 now released you may be eager to try it out.  One of the best ways to do this in my opinion is to set it up on a virtual machine.  You could certainly do this with VMWare or Hyper-V but my new preference for creating VMs is Azure.

The great thing about it is you don’t even have to go download the software locally because they already have a template in the Azure Virtual Machine Gallery ready to go for you.  Here’s the steps to create your own SQL Server 2016 CTP2 virtual machine.

  • Go to and click Portal. Then either sign in with your existing account or sign up for a free trial.
  • Go to the Virtual Machines page and select New. If this is your first virtual machine you could also select Create a Virtual Machine in the middle of the screen.


  • Choose to create the virtual machine by selecting From Gallery.
  • In the gallery use the search bar to easily find the right VM.  I just searched 2016 and found the one I was looking for and then clicked the arrow to continue.


  • Provide the details needed for the Virtual machine configuration dialog.  You’ll need to come up with a name and login details for you VM. You’ll also be asked what size virtual machines you need.  This configures the resources and determines the cost to you. You can change the size at a later time if you’d like. Remember, outside of the storage space you only pay for the time that the virtual machine is actually on so if you boot it down when you’re done using it then you’re just paying for the cost of the virtual hard disk storage.
  • Next you’ll be prompted to create a cloud service and storage account for the VM. If you have previously created these you can assign this VM to an existing cloud service and storage account or create a brand new one here. Click the arrow to continue.


  • The last configuration screen will ask you about additional products you would like installed like antivirus an antimalware software. At the very least install the VM Agent, which is the default, and then click the check to create the virtual machine.
  • You should see the virtual machine start provisioning.


  • Once it’s complete you can launch the virtual machine by clicking Connect on the bottom of the screen. This will download a remote desktop (.rdp) file that once open will connect to your new Azure VM.
  • If you receive and error when opening this file it likely means that the VM is still provisioning.
  • Type in the username and password you provided when you created the VM to login.

Now that you’ve got SQL Server 2016 ready to go you can now try out many of the new features that are detailed here.

Power BI with Azure SQL Database

**Information is current as of 5/21/2015**

Recently Azure SQL Database was added as a new connection to the Power BI Preview. Read the notes about how it can be used here. The things I found especially worth noting were:

  • Every action sends a query back to the database. So you’re seeing the data real time from that database
  • Dashboard tiles are automatically updated every 15 minutes.  Meaning you don’t even have to schedule a refresh
  • Q&A is not available with this direct connect to the live data :(
  • The direct connection and refresh only works when building reports directly from and does not work from the Power BI Designer

Keep in mind these things may change but that’s how it exists in preview now.  I thought I’d give it a try and walk you through a quick example of getting connected to a Azure SQL Database that’s been made available free to the community.

Getting Connected

  • If you haven’t already, you’ll need to sign up for the PowerBI preview at
  • Once you’re signed up, you can sign into at to get started
  • To create a refreshable data  source from an Azure SQL Database, select "Get Data"


  • Then select Azure SQL Database as your data source


  • If you don’t have any Azure SQL Databases but would like to still test this out then use the one that has kindly provided with login details to the community for free here. This is the AdventureWorks database with several other supplemental tables added to it as well.
  • Connect to your Azure SQL Database by providing the Server, Database, User and Password then click Connect.
  • This will create a new Dataset to the AdvnetureWorks2012 Azure database and a new Dashboard (If you didn’t already have one selected) called Azure SQL Database


  • Select the dashboard and you will see a placeholder tile for the dataset.


  • Click the placeholder to explore the data or click the dataset and it will launch the report designer where you can begin building a new report.
  • If you’re using the sample provided by SQLServerCentral then you may be a little overwhelmed with all the tables listed to do my demonstration.  I used the Categories, Customers, Products and Order Details tables to produce a report that looks like this.


  • Once you’ve built a report make sure to Save it.
  • Once the report is saved you can pin a visualization to your dashboard by simply clicking the Pin to your dashboard button and it will appear on the dashboard.


  • If you return to the dashboard you will see any pinned items now appear there.


  • These will also appear on mobile devices that Power BI currently is available on.

Power Query – Controlling M Query Functions with User Driven Parameters

Have you ever had a user run a query against one of your largest tables only for them to immediately filter the results in Excel to show the last years worth of data? All of that data brought across your network and then immediately filtered out. Or maybe Excel just can handle the amount of unfiltered data they’re trying to return.

In this post I’d like to show you a way to solve this problem with Power Query in a solution that can dynamically filter your data returned based on user driven parameters.

In the scenario that I will demonstrate, rather then returning an entire result of all the company’s employees, I just want to return a list that show employees with a hire date within a certain range of dates that I or a user will provide. Using Power Query’s ability to make queries into Functions I’ll give my user the ability to provide the range of their choice

Connect to the Data

  • In my example I’m going use a SQL Server table as my data source but it could be any type of table. To connect to a SQL Server table from the Power Query tab in Excel select From Database > From SQL Server Database. For my example I’ll be using the AdventureWorksDW sample database.


  • Next you will be prompted to provide your Server and Database names where the table is located. Type these in then click OK.
  • Once you provide the Server and Database name you will also be prompted for the credentials you will use to access the data then click Connect.
  • The Navigator pane will appear showing all the available tables.  If you’re following my example with the AdventureWorksDW sample database then choose DimEmployee and then click Edit. This returns back the table to the Power Query Editor. **Note** This could be done on any table from any database.

Making the Query a Function

  • What I’d like to have my users do is return back this list of employees but only when the HireDate column falls within a range of values that they provide. To do this we’ll start by applying a hardcoded value to filter to the HireDate column. Find the HireDate column and apply a filter by clicking the down arrow next to the column and then Date Filters > Between


  • The range of values you filter on will depend on your table but for the DimEmployee table in AdventureWorksDW I used the following filter then clicked OK.


  • This simple places a filter on the query. If we want to make the filter dynamic we need to modify the M Query that’s behind the user interface. Go to the View tab on the Query Editor ribbon and select Advanced Editor. This will open the query window where you can modify


  • Next modify this query to add in a start date and end date parameter with the code in red below.

(startdate, enddate)=>
    Source = Sql.Database("localhost", "AdventureWorksDW2012"),
    dbo_DimEmployee = Source{[Schema="dbo",Item="DimEmployee"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimEmployee, each [HireDate] >= #date(2000, 1, 1) and [HireDate] <= #date(2002, 1, 1))
    #"Filtered Rows"

  • Once the parameters are created you can reference them in the query to replace the hardcoded value in the filter with a dynamic value from the parameters.  Modify the query with the code in red below then click Done.

(startdate, enddate)=>
    Source = Sql.Database("localhost", "AdventureWorksDW2012"),
    dbo_DimEmployee = Source{[Schema="dbo",Item="DimEmployee"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_DimEmployee, each [HireDate] >= #date(Date.Year(startdate), Date.Month(startdate), Date.Day(startdate)) and [HireDate] <= #date(Date.Year(enddate), Date.Month(enddate), Date.Day(enddate)))
    #"Filtered Rows"

  • This will convert the query into a function. You can test this function by clicking Invoke and then you will be prompted to provide date values to filter on.



  • If you did invoke the function make sure you remove the Invoke step before moving on. You can do this by clicking the delete icon in the Applied Steps pane.


  • This should return the query back to a function ready to be invoked. Now, go to the Home tab on the Query Editor ribbon and select Close & Load.
  • This will save the M Query function into the workbook but does not return any results yet.  Just how we want it! Our next step is to pass the values we want into the function.


Making it User Interactive

  • Go to a blank spreadsheet and create a simple Excel table that has a StartDate and Endate column with one row of values like this:


  • To make it so our users can type a value in this Excel Table and pass it into our function we need to take bring this small table into Power Query. Select one of the cells inside the table and on the Power Query tab select From Table. This will take the content of this table and bring it into the Power Query Editor.


  • To pass these two values into our function go to the Add Column tab and select Add Custom Column.
  • Write the following formula to connect the DimEmployee function created earlier to the dates we’ve now defined in the Excel table then click OK:



  • If all your default settings are turned on inside Power Query you will probably get a privacy warning pop up.  This is because you’re working with two different data sources (1. SQL Server Table, 2. Excel Table) and there’s potential privacy concerns. In our scenario there are no legitimate privacy concerns so I’ll hit Continue. I also set the data sources to Organizational because the data sources should be contained within my company. Read more about Power Query privacy settings here.


  • Once the privacy settings are configured Power Query will add in a new column just called Custom (We could have renamed it previously). Click the Expand button next to the Custom column, uncheck Use original column name as prefix and then click OK. This will bring back all the rows that have hire dates between our date range.


  • Go ahead and remove the StartDate and EndDate columns from the query now but multi-selecting them and then right-click and select Remove Columns.
  • Next, rename the query in the Query Settings pane to Employee Data then click Close & Load on the Home tab.
  • You should now have two spreadsheets (It would obviously be a good idea to rename these) in your workbook.
    1. Sheet1 that has the Excel table with the date range values
    2. Sheet2 that has the results of the Power Query query. This data could have optionally been send to the Power Pivot Data Model
  • Now go back to Sheet1 and change the date range values in the StartDate and/or EndDate columns. After making this change the next time the Power Query query is refreshed it will pick up the results from the table to filter on saving your users from query a really large table when they only need a subset of the data.

Finishing Touches

If my users aren’t very familiar with Power Query and don’t know how to refresh their queries then we can build a quick little macro to provide a button that does it for them. Using a technique I learned from Ken Puls (blog | twitter) in a few short steps our macro will be done. In Ken’s post titled Refresh Power Query with VBA he shows how to refresh all Power Query queries in a workbook with a little VBA script.  I’m going to take the same principles he shows but just refresh the query I care about.

  • In your workbook press Alt + F11
  • Right-click on VBAProject(Book1), this may be named something different if you’ve saved with a new name, in the Project Explorer and select Insert > Module.


  • Use the following VBA script to refresh the workbook connection for our Employee query we created earlier (If you named your query something different you may need to adjust the section highlighted in red):

Public Sub UpdateEmployeeQuery()
‘ Macro to update my Power Query script(s)

Dim cn As WorkbookConnection

For Each cn In ThisWorkbook.Connections
If cn = "Power Query – Employee" Then cn.Refresh
Next cn
End Sub

  • Hit close on the VBA window.
  • To manually try the new code hit Alt + F8 and you will be prompted to run the script. Select the Macro we just created and click Run.


  • You should notice this kicks off the refresh of our Employee query.



  • Click somewhere in the worksheet that you want the button and then select the Macro we created to assign to the button. Click OK.


  • Click inside the button to rename it and then you’re all set!
  • Now all you have to do is change the values in the table and click the button to refresh the results of the query. This works if the results are rendered to an Excel spreadsheet or Power Pivot Data Model.


As long as the data source and types of transforms support it Query Folding will still be utilized with this method. If you’re  curious about what Query folding is read more about it in this Matt Masson post.

I’ve made this example available if you would like to download it: User Driven Parameter Example.xlsx


Get every new post delivered to your Inbox.

Join 48 other followers