Switch from live connection to DirectQuery - Can I edit tables in Power Query?

[cheryl0316]

I created a Power BI report that connects to a semantic model via OneLake catalog. I’d like to make changes to the model, so I need to switch from Live Connection to DirectQuery—but none of the tables are showing up in Power Query. Why? How can I make changes to the tables through Power Query?

[danextian]

I havent tried this myself but this might work.

• add another data source - this will prompt you to create a local copy of the model and connect via direct query
• check if the tables are editable in the query editor. If not, delete all tables - the diagram view allows selecting all tables so you won't have to click each one by one.
• reconnect to the semantic model via direct query/sql endpoint.

Note: you might have to recreate some features that were available using live connection. You might also need to recreate the relationships.

[cheryl0316]

Thank you for your reply - I’m trying to create a centralized semantic model so that users can live connect to it and see all the default tables, relationships, and measures.

I added a table using DirectQuery and this table is editable in Power Query Editor. Then I connected to the centralized semantic model via OneLake Catalog. When I open Power Query Editor again, the tables from the centralized semantic model are not shown. 

[v-saisrao-msft]

Hi @cheryl0316,

Thank you @MasonMA @danextian, for your insights.

When you connect to a published semantic model in Power BI using Live Connection or DirectQuery, the model is read-only. This means its tables won't show up in Power Query and you can't transform them. This is known behaviour. To make changes, you should edit the source dataset, import the data into a new PBIX file, do your Power Query transformations there, and then publish it as a new semantic model. Reports can then use this model for consistency, with all changes managed in the staging dataset before publishing.

Thank you.

[MasonMA]

Hello @cheryl0316 

When connected via Live Connection/DirectQuery, Power BI treats the semantic model as a read-only endpoint. If in your situation that I'd need to edit tables in Power Query, i would try creating a 'Staging Dataset' and 'Import Data' into a new PBIX. Then publish it as a new semantic model and reconnect those reports.

Hope this gives you some ideas:) 

[cheryl0316]

Thank you for your reply - I’m trying to create a centralized semantic model so that users can live connect to it and see all the default tables, relationships, and measures.

As you mentioned, Power BI treats the semantic model as a read-only endpoint, so it seems users can't edit any tables once they connect via Live Connection or DirectQuery to the centralized semantic model. In that case, would a centralized semantic model still be useful? What do you think?

Also, how can we reconnect existing reports to a newly published semantic model?

[MasonMA]

Centralized semantic model is valuable for its consistency and governance, by this i mean all reports use the same logic (measures, relationships, security), and it provides a single source of truth with controlled updates. However, model users cannot edit the model in their reports.