No, SSAS Tabular mode don’t support PowerQuery connections… (yet… But I’am confident that will be in the future)
But I was happy to verify that it’s very easy to change the connection and point into another table in a supported provider (ex: a SQL Server DW).
It’s true that you will loose all the transformations made in powerquery, but in the scenario that we are converting a self-service BI model made in Excel into a more enterprise level BI application where the transformations need to be made by a more “professional” ETL tool like SSIS tool, it’s good to know that it’s possible to convert the connection very easily.
PS – This is not the same history with Linked Tables in PowerPivot, when converting into a tabular database those tables will end up as Pasted Tables which are very hard to update, so stay away from Linked Tables… As an alternative I find preferable to create a powerquery into the Excel table and then load into the model.
Start converting the powerpivot workbook into a SSAS Tabular project:
Then go to Model –> Existing Connections, and you’ll find all your PowerQuery connections:
Try to process one of them, and you’ll get this error:
Now you need for each PowerQuery connection to change the connection provider into a supported one and point into a object that has the same name and schema (if it doesn’t have the same schema/name you will have to change the table partition query).
In this case I have a table named “Employee” with these columns:
Create a table in your datawarehouse with the same name and schema:
Edit the “PowerQuery – Employees” connection and change the provider:
Connect into my SQLServer DW:
The simply process the table and it will process sucessfuly, and the new data will be loaded:
