Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
akito
Frequent Visitor

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

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 🙂

 

akito_0-1646195963501.pngakito_1-1646195973142.png

akito_2-1646196043054.png

Regards,

Akito

2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
KevinAlexx25
New Member

Pude resolverlo de la siguiente manera Akito,  y para cualquier persona que le pase este problema y que no puedan solucionar el Direct Query. Ubique la sección donde dice configuración de origen de datos, busque el Direct Query (Osea los datos que tienes cargados). Luego, le dice cambiar origen y volvi a crear la conexión. No se pierde nada solo volvi a cargar nuevamente las tablas que tenia y ya. En mi caso el problema era que tenia el nombre de una tabla que tenia otro nombre y cuando lo actualice me decia que esta tabla no existia. Entonces solo la quite y coloque la que habia cambiado y listo.

 

I hope this help ✌️

akito
Frequent Visitor

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  ?. 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors