There's a frustrating bug with Composit Models. Append Queries are somehow always flagged as direct queries, so you can't do transforms on any append query. You also can't load any append queries when you have the Composit Models preview feature activated.
Here's a short walk-through to produce the errors and issue:
- Activate the Composite Models preview feature in Power BI Desktop and Restart PBI Desktop to take effect.
- Get Data from Excel and Edit 2 spreadsheets that you want to append.
- In Power Query, turn off / disable the "Enable Load" on both imported spreadsheets.
- Create an Append Query as New and append both spreadsheets to a new query.
- Make any transforms you want or none at all.
- Apply and load the append query
- An error occurs on the load "This query contains transformations that can't be used for DirectQuery."
- Even if you remove all other steps except for the 1st step to append, you get the error.
Another realted issue occurs if you turn on the "Manage Aggregations" preview feature. With that option activated, you see the same error in the query as a yellow top warning banner in the append query. It gives you a button option to click to "Switch all tables to import mode" But this does not work at all. If you click it and then select another query and then reselect your append query it's back. Also loads still don't work.
To resolve the issue. you have to do the following:
- Deactivate both preview features for Manage Aggregations and for Composit Models.
- Restart Power BI to take effect.
- Open your PBI file and open Power Query
- Delete your append query
- Recreate your append query
- Everything works as expected.
To summarize, the only way to fix the append query is to deactivate preview feature and remake the append query with the feature off.
Analysis: Somewhere in the PBI file the modes for the queries are getting switched, and stored to directquery, but are not able to be changed. This appears to be a bug, as the whole point of doing a composite model is to be able to mix and match directqury and import queries in a single model. However this bug prevens you from using append queries.
mattlee
on:
Small multiples not fitting to scale
on:
Connection Timeout to Azure Analysis Services (AAS...
on:
CMD Prompt Popping Up During Changes