Composite Models: Can't append Tables with different storage modes

[Anonymous]

Using the new 'composite models' feature introduced in July 18, I have created two identical tables.

One is Direct Query (Last Days Data), the other is imported (Last Weeks Data). I wanted to append these tables into a new table which would result in a union of the data. After saving from the Query Editor, I recieve an error message:

'This query contains tranformations that can't be used for DirectQuery'

[v-qiuyu-msft]

Hi @Anonymous,

I have reported this issue internally: CRI 81719020. Will update here once I get any information. 

Best Regards,
Qiuyun Yu 

[jmannhardt]

I am having a similar issue, where the query editor is defaulting SQL Server Database to DirectQuery, even when set to Import in a variety of ways.

I've successfully built a different model that is using SQL Server On Prem and Azure Cosmos DB and appending the datasources together, so unsure why the inconsistent experience.

https://community.powerbi.com/t5/Issues/DataSource-Automatically-Switching-to-DirectQuery/idi-p/5014...

[jmannhardt]

@Anonymous 

 An interesting work around I've discovered is to build the model in Power BI Desktop optimized for Report Server, and then open in Power BI Desktop.

[brianhartman]

We have received several reports of issues similar to this.  The underlying issue is that the query is being mistakenly classified as DirectQuery rather than import.  We're making some improvements to this such that this does not happen, but they aren't available just yet.

In the mean time, you can work around this by creating a query that must be import, such as one based on a CSV or Excel file.  Once that query is loaded to the model, go back to the query editor, right click on the query, select Advanced Editor.  Replace the query text for the CSV/Excel query with the query that does the append.