Cannot read from the table in power query when connected as DirectQuery for Power BI datasets

[MikhailDom]

Hi All,  

I have a problem.

1) I connected using DirectQuery for Power BI datasets, as described here https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-datasets-azure-analysis-s...

2) Clicked make change  and add local model

After that I can create dashboards using the data.

However, when I try to create a new power query and read the data from one of the existing tables I get the following error.

(I want to write a new query to join and combine several tables)

Is there any other way to read data from tables connected via  DirectQuery ?

[AlexisOlson]

You can't pull tables into Power Query from your data model. The flow of data goes the other way.

Data Sources --> Power Query --> Data Model --> Measures and Visuals

You're still connected to that table though and can use it for building visuals.

What is the goal you're trying to achieve by attempting this?

[MikhailDom]

Thank you for the reply.  I'm trying to achieve the following configuration

1) Dataset A    is published to Power BI service and connected to the database via on-premise gateway. It is kind of core dataset.

2) There are several teams  who create their own datasets (Team 1 dataset, Team 2 dataset , etc.) using data from Dataset A .  These teams may want to place additional logic ,like join tables create grouping tables, calculated attributes in power query

[AlexisOlson]

It's not a DirectQuery, but it is possible to load tables (or whatever DAX query on Dataset A you like) by connecting to it in the query editor as an Analysis Services Database. Then you can do whatever you'd like with it.

See my post here for an example of such a query:
https://community.powerbi.com/t5/Service/How-can-I-set-up-a-scheduled-refresh-for-tables-that-are-DA...

(If you just want a plain table from the model, then the DAX query is simply "EVALUATE TableName".)

[MikhailDom]

Thank you!  I've chosen the solution with data flow, because I can use power query (M language) There.  Your solution is DAX based.  Maybe it would help me in some other case.  Thank you for you help.

[AlexisOlson]

Just to be clear, it's a DAX query within an M language query. As I mentioned, you don't necessarily write any more DAX than EVALUATE and a table name.

[mahoneypat · Accepted solution]

Sounds like you need to request that a Dataflow is created that has the table(s)/queries from Dataset A. That way, you can do downstream transformations, if needed.

Pat

[MikhailDom]

That worked for me. Thank you!