Edit: This feature has been deprecated since 1st March 2019 unfortunately: https://azure.microsoft.com/en-us/updates/azure-analysis-services-web-designer-to-be-discontinued/ – Microsoft is working on replacement of the functionality to move pbix-files to SSAS-instances, but no solution yet.
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:
- Import the Excel file in Power BI Desktop, save and close the pbix-file
- Open Azure Analysis service, open the Web Designer and create a new model where you import the pbix
- Open that model with Visual Studio (this will actually create a download that holds the VS-file)
- 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 😉