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
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
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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 ✌️
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |