Excel Workbook Connection to Local Power BI Desktop

Today I am sharing an easy way to use Power BI Desktop as a SSAS tabular server on your local PC.  I find increasingly that I am building data models in Power BI Desktop and yet I still want to use Excel to analyse the data some of the time.  If I load the PBIX workbook to the Power BI Service, it is easy to use “Analyze in Excel” to query the data model.  But there is currently no standard supported method to do the same thing using Power BI Desktop as a server (see my suggested idea to Microsoft at the bottom of this page for more about that).  Until then, we need to use a “hack” to complete this task.

I have previously shared a way to:

  • Open DAX Studio on your PC
  • Use DAX Studio to extract the Port Number of a running instance of Power BI Desktop
  • Manually enter this information into Excel to connect to Power BI Desktop and use it as a local SSAS Server instance
  • Query the data model that exists in the open Power BI Desktop file with a Pivot Table in Excel.

The problem with the above approach is that once the Power BI Desktop file is closed, the connection string becomes invalid.  Next time you want to use Power BI Desktop as a server you must go through the entire process again plus you need to alter the connection string that was previously created – hardly a scalable solution.

An Excel VBA Workbook Template to Solve This Problem

Local Host Workbook

Today I am sharing a tool that I built recently that completes the entire process automatically in Excel using VBA and Power Query.

Disclaimer:  I am providing this tool free of charge, without support, and do not warrant that it will work for you.  This is not supported by Microsoft and could stop working anytime.  If it works for you, then great – if not then it is unfortunate :-(.  Maybe you can deconstruct what I have done and make it work for you.  I built this using Excel 2016 and I have tested it on 2010 and 2013 and it works (Please make sure you have the latest version of Power Query installed).

You create a new blank Excel Workbook using my template (from personal templates), then click the “Refresh SSAS Connection” button.  The tool will then:

  • Detect an instance of Power BI Desktop running on your PC (there must be 1 and only 1)
  • It finds the port number and database name of the running instance
  • it modifies the existing connection string in the Excel Workbook to repoint it to the current running instance of Power BI Desktop.
  • If the workbook already contains pivot tables that were previously built using the same data source, they will simply reconnect and work as you expect.
  • If this is the first time you have used the template, you will have a blank Pivot Table connected to the Power BI Desktop data model.
  • You should only have 1 Excel Workbook and 1 Power BI Desktop file open at any one time (as they all use the same connection string).

I am not planning on covering in detail how the tool works.  I am making it available to use and modify as you see fit.  If you want to look into the VBA code and the Power Query to see what I did then of course this is fine by me.  If you want to improve it and give me back the improvements under the same “share and share alike” rules, then please do so as long as you leave my name comments in the file untouched.

How to use the Template

You can download the template here.  I have put it in a zip file so that I can keep the same link but change the name of the actual file over time (to manage version numbers).

The file is a macro enabled Excel Template workbook.  You need to place it in your Personal Templates location.  To find this location, in Excel go to File\Options, then navigate to save (1 below) then check the location (shown in 2).  If you don’t have a current template location, you can set your own.  Note Excel 2010 has a different file location (Google it).

image

To create a new workbook that uses this template, in Excel go to File\New, select the personal templates option (shown as 2 below) and then select the template.

image

I would love to hear what you think and find out if this is a useful tool for you.

Let’s Ask Microsoft to Make this a Standard Feature

I was talking to Avi Singh and he suggested that I request this as a standard feature.  I have set up an idea that you can find and vote for here.  If we get enough votes for this feature, Microsoft may build out this capability into the core product(s) so it is easier for everyone and fully maintained for future releases.

Relevant Articles

This is the first post in a series of articles.  You can read the other post in this series from the following link.

Part 2

Share?

Comments

  1. Wow! That’s a great tool. Powerbi.com does have “Analyse in excel”, but the connection is always slow. Yes, we should all vote for having this as a standard feature.

  2. Good stuff Matt! I love Power BI. And Power BI Desktop is great, but often while building the model want to quickly dive into Excel to debug something (usually a DAX measure or sometimes investigate Power Query issues). No better way to do that than Excel.

    And you have my 3 votes for the feature!

  3. This is amazing, Matt. Thanks a lot for making this automatic. This feature is so much desirable as it eliminates the boundaries between Power BI Desktop and Excel. Now, I can say that I’ve got a perfect testing environment for all my Power BI models, just like SSAS. Thanks again. Wonderful work!!! (:

  4. I remember one of the guys at Microsoft showing me how to do something similar but manually (think it was Jeff Wang) but this template will help no end. Yes, you definitely need some kind ease of debugging as I find I’m publishing constantly to discover my results at the user (web) end so this pain point has me all ears

  5. Thanks Matt. The problem when I conversted my PBIX file to excel pivot table, it is not letting any thing in values category.

    • Nazim. Do you have measures? You can’t use implicit measures when you have a pivot table in a Excel connected to a power bi desktop file. Write a measure like total sales = sum(sales[qty]) in power bi desktop and then refresh the connection to Excel. You will see it there.

  6. Hi Matt. Just noticed that “Freeze Panes” causes Excel to crash after connecting to Power BI model. I’m using Excel 2016 64-bit (Version 1610, Build 7466.2038)

  7. It’s working fine after some renewed attempts. I guess some bug with excel. Kindly ignore my earlier post. Thanks.

  8. Also, I’m clearly noticing that the performance of same data model in Power BI is far better than the one in Power Pivot. Sounds strange to me but yes.

    • Maybe because the compatibility level of models from PowerBI Desktop is 1200 the same of the SSAS Tabular engine of SQL Server 2016 Analysis Services (SSAS)and the compatibility level from Power Pivot models is 1100 or 1103.
      Server 2016 Analysis Services (SSAS), compatibility level 1200, includes many new enhancements providing improved performance.

  9. it was about time that someone do it 🙂 Excellent Job mate specially for the VBA code

    one practical scenario is to used this with cube formula, as the data will be persistent, although static which is good for fixed report format.

    kind of personal SSRS server 🙂

  10. Hi Matt,

    Another great post.

    I am having trouble getting this to work with Excel 2010 (32 bit) and I am running Power BI Desktop (64 bit).
    When I click the button “Refresh SSAS Connection” I get a VBA Run-time error -2147417848 (80010108) and it says Method ‘Refresh’ of object ‘Workbook.Connection’ failed.
    When I debug the error it highlights the line of code: ActiveWorkbook.Connections(“PBID”).Refresh as the line of code causing the issue.

    Any idea what may be causing this error issue?

    Thanks

    • That’s interesting. I wonder if it is related to the 32/64 bit issue. Imke Feldman alerted me to a way to complete this task without the step you mention that failed. I have a note to look into that. I will let you know when it is done

  11. @Matt – This is fantastic – you are a Genius – and you have my 3 Votes.
    I have been waiting for this for a very long time. The PQ Team keep promising that this is coming soon….
    I wish Avi had pushed MS to allow people to connect to the PBI Desktop first and Cloud Later

    1) Is there now way to freeze the Port No or Is there a way to connect to the File (DB) rather than the Port #
    2) Will this work if the File is kept on a Shared Folder

    • 1) There is no way to freeze the port number, nor the GUID for the DB actually. And no there is no other way to do it (that I know of).
      2) which file are you referring to? The Excel file? I don’t see any reason, as long as the Power BI Desktop instance is running on the same PC as the user that is accessing the Excel file. As for running the PBI Desktop file on a server – that is a very different thing and no this won’t work (under the current design).

  12. That’s brilliant! As Many have pointed out, now you have a way of evaluating DAX equations created in Power BI by seeing the underlying data.

  13. Hey, Matt. Your dropbox link for the template file seems expired. Do you mind re-upload? Cheers!

  14. Matt, this is awesome. got some runtime error ‘1004’ [datasource.notfound] file or folder.
    I’m using excel 2013, 64bit, PB desktop 64 bit. thanks for the help.

  15. Hey Matt! Thank you so much for sharing! App is brilliant.
    Do you happen to know, what can cause such problem:
    when I hit “refresh SSAS connection” button it gives me message “you must have exactly 1 instance of power bi”.
    and I’m sure that I have only one power bi opened. I rebooted my comp but the problem still stays there.

    I work on windows 10 64 with Excel 64 and Power BI 64 (april 2017 release).
    I use your file: Local Host Workbook ver 2.4.xltm

    Any Ideas?

    • I’ve never seen that before. My best guess is you have an old instance of SSAS in the temp folder that didn’t get deleted. You could try doing a disk cleanup and delete temp files. If that doesn’t work, send me an email an I will give you a further suggestion.

      • As luck (unluck) would have it, this same thing just happened to me then. My hunch is right, it happens if Power BI ends unexpectedly and doesn’t clean up after itself. You need to delete the old temp files. Simply go Start\Run and paste the following folder into the run dialog, then click OK. Delete all the sub folders you can see. If there is one that is still active it wont allow you to delete that one, so just click cancel for that one. Then it should work.

  16. Change PowerPivot & PowerQuery data sources from local dataset to Power BI Service?

    I have a very large Excel workbook with dozens of Pivot Tables that we use for daily sales reporting. The data model has numerous tables with calculated columns and measures that have been built over time. Many worksheets have multiple slicers so the worksheet is huge and takes forever to refresh. We have replicated the data model in Power BI Desktop and published it to Power BI where it now refreshes daily without a hitch.

    So, how do I redirect all of the data sources links in my Excel workbook to now point to the Power BI service instead of the local dataset? I can do it manually but that will take forever to accomplish. I’m hoping someone has an idea how to automate that process.

    • I work on the same kind of worksheets at the moment. As long as you are working with cubereferences it is quite easy.

      Based on your Power BI servicemodel, you can analyse in Excel, then create a small pivottabel and convert that to a cubeformula.

      Next you can copy all sheets from the old Excelfile, that only includes cube formulas to your new Excelfile and do a search and replace of the “ThisWorkbookDataModel” to the name of your PowerBI Datamodel (in my case called “PBIDatamodel”.

      That works great until I copy a full Pivot Table, where I for some reason is not allowed to change the datasource, but is told that the PivotTable is only a connection string. If somebody can tell how to change that connection string to the Power BI Service datamodel, I think that is the full answer to your challenges.

      • I have spoken directly to the Excel Dev team about doing this (repointing the pivot table connection to Power BI). They looked into it and replied that “it can’t be done”. I do however know it is possible because the guys at https://www.power-planner.com know how to do it. They will do it for you for a fee. You can contact Per at the URL above

      • Hi Jørgen,
        You can change the connection of a pivot table if you go to Data–Connections–Properties (of the connection to the service) and copy the entire connection string, then go to Data–Connections–Properties (of the local connection which the pivot table(s) are using), and overwrite the connection string with the one from the service.

        • Forgot to mention that is is under “Definition” once you are in the Connection Properties. This method in fact is easier than find/replacing in your cube formulas. Just copy the string from a connection to the service and overwrite the connection on your locally-connected workbook and the whole thing (pivot tables, cube formulas, etc) is done at once.

  17. Hi Matt, this looks like an awesome tool, but I am now on the Windows Store version of Power BI Desktop and it appears that the workbook does not detect that instance of PBI Desktop. Any chance this will get updated for the new application?

      • Thanks for the Tip Off Mike. I did a bit of super sleuth work and I found the new temporary location for the instance of Analysis Services. I have created a second copy of the template (in the download link) and it now has one for the old version and one for the store version. I could consolidate it but it would create some potential issues that I think I will avoid.

Leave a Reply to Maxim Uvarov Cancel reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x