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 😉