Changing data source to or from SQL to a file removes all data groups / bins from the model

[HenW]

Changing the data source in a Power BI Report from an Excel file (or CSV file) to a SQL server query causes any data groups / bins that have been set up to be removed from the model. This happens when changing the data source from or to SQL.

[v-yetao1-msft]

@HenW 

If you connect to SQL with Direct Query mode , then change the data source , the data you reference is definitely a problem because DQ's connection pattern is to send queries to the data source, not to store the data to PBI Desktop. Once your data source changes, there is no way to get the data from the SQL data source .

Best Regards,
Community Support Team _ Ailsa Tao

[HenW]

@v-yetao1-msft 

Thanks for your response. I appreciate that but in this instance I am not using direct query. In fact when loading data from SQL this is using a stored procedure which would not work with direct query.

When I encountered this issue I had a Power BI file that loaded data from a single excel file and contaned several groups / bins. Within the query / transform data element I change the source to be SQL Stored Procedure isntead of the excel file. The actual data conent is identical. It was my understanding that the query and data load side is completly seperate from the data model and provided the data flowing through has the same structure the data model should be the same. However changing the data source caused all the bins / groups to disappear from the model.

I tried changing the data source to be a CSV instead which worked as expected I only get this issue when changing from a file to SQL or from SQL to a file.

Thanks