Why I cant see my Direct Query tables in Power Query - (Power BI Dataset)

[akito]

Hi All,

I am not able to see tables in Power Query that are from Direct Query stoarge connection that is linked to Live Power BI Dataset. I only can see my tables from excel as I have mixed storage connection (Direct Query and Imported).

I am trying to create a new table from tables (Append or Merge) that are in Direct Query storage. 

Note: I am pretty new in Power BI -  who is trying to switch from Tableau to Power BI 🙂

Regards,

Akito

[BA_Pete · Accepted solution]

Hi @akito ,

In Power BI, you only see tables in the Data section if those tables are going to be stored in a static state (i.e. IMPORTed) within your report. This Data screen is showing you all the tables/data that are stored within your report after refresh.

With Direct Query, no tables/data are stored within the report after refresh, so you can't see them here.

When using Direct Query, each of your report visuals generates its own SQL query specific to what that exact visual needs to show and sends it directly to the source when the report loads and/or when filters/slicers are changed. This allows for reports to display near-live data in visuals, but also significantly restricts the transformations and general control/visibility of the data between the source and visuals.

If you are happy to display data that is not up-to-the-second live, then you should be using IMPORT as your data acquisition method, which allows you full control over data structures and transformations.

If you need to have up-to-the-second live data, then you will want to use DIRECT QUERY or LIVE CONNECTION, but you will probably need to get the data structured at source, as you will not have significant control over it from within Power Query/Power BI.

Transformation operations that you mention, such as Append and Merge, would almost always need to be done on the source when using Direct Query, and definitely when using Live Connection.

Pete

[akito]

Thanks @BA_Pete , that make sense. maybe another quick stuipid question :). I cant see all the data fields in DAX but only calculated ones. Is this because of my dataset nature  ?. 

[BA_Pete · Accepted solution]

Hi @akito ,

I suspect this is due to the fact that you are using a data acquisition method that DOES NOT store data with the report, so the original fields are not visible. However, calculated columns must be materialised into memory at runtime, so ARE essentially 'stored' with the report and are, therefore, visible.

Pete

[BA_Pete · Accepted solution]

Hi @akito ,

In Power BI, you only see tables in the Data section if those tables are going to be stored in a static state (i.e. IMPORTed) within your report. This Data screen is showing you all the tables/data that are stored within your report after refresh.

With Direct Query, no tables/data are stored within the report after refresh, so you can't see them here.

When using Direct Query, each of your report visuals generates its own SQL query specific to what that exact visual needs to show and sends it directly to the source when the report loads and/or when filters/slicers are changed. This allows for reports to display near-live data in visuals, but also significantly restricts the transformations and general control/visibility of the data between the source and visuals.

If you are happy to display data that is not up-to-the-second live, then you should be using IMPORT as your data acquisition method, which allows you full control over data structures and transformations.

If you need to have up-to-the-second live data, then you will want to use DIRECT QUERY or LIVE CONNECTION, but you will probably need to get the data structured at source, as you will not have significant control over it from within Power Query/Power BI.

Transformation operations that you mention, such as Append and Merge, would almost always need to be done on the source when using Direct Query, and definitely when using Live Connection.

Pete