Migrate a Power Query or Power BI file to a local SSAS instance

In Visual Studio there is a wizard to migrate an Excel Power Pivot model to a SSAS model. But this will not bring over the M-queries unfortunately. But there is a workaround to achieve this. It requires SQL Server 2017 or higher:

The steps:

  1. Import the Excel file in Power BI Desktop, save and close the pbix-file
  2. Open Azure Analysis service, open the Web Designer and create a new model where you import the pbix
  3. Open that model with Visual Studio (this will actually create a download that holds the VS-file)
  4. Open that file in Visual Studio, load the data, build and change the deployment target from Azure to you local SSAS-database before deploying.

See how it goes:

Warning: There are some limitations for the M-functionalities in SSAS (see here for example: General Overview by Microsoft or Use your own SQL … by Chris Webb), so you might want to give it a thorough test before rolling out. There are missing a lot of data sources currently, like web-queries for example who will hopefully soon be added as well.

This method has been described by Soheil Bakhshi here before: http://biinsight.com/import-power-bi-desktop-model-to-ssas-tabular-2017-using-azure-analysis-services/ 

Enjoy & stay queryious 😉

Comments (6) Write a comment

  1. Hi,

    Thanks for the clever solution. I would like to know if this workaround is breaching any licensing if you have a developer edition?
    Due to my limited amount of Analysis Services, I would like to know if other people would be able to connect to this model is it’s localhost?




  2. Hi Imke,

    While importing from Excel to Power BI Desktop – there is a problem if you have a local table that has “\” in any cell for Example in a Parameter table have the file Path C:\DATA

    So you must remove the “\” in Excel, and import it in to Power BI Desktop and then put the “\” again in Power BI Desktop


    • Thanks sam for mentioning this.
      This doesn’t give a problem on my machine, but it could well be that others run into this problem as well.
      Cheers, Imke


  3. @Imke – download the 2 excel files from the below link – Keep the Report file on your desktop and the DB file in C:\DATA
    Now open Power BI Desktop – Import the Queries and Data model from the Excel file called Report.
    Go in to Edit queries and click on a Table called “P” – you will get a Error
    “We found an invalid escape sequence in JSON input”


    The issue disappears moment you remove the “\” in the Table P in Cell B2 of the the Excel file Report
    and manually put it back in Power BI Desktop


Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz