Forum Discussion
Query Terminology & General Ideas
Kind of having a brain freeze - hoping the community will aid:
1. Power Query: isn't this a term specifically in excel? I see it used here alot but I don't think it is actually in PBI Desktop - but rather is Query Editor. true or no....? I should know this but am fumbling the ball here.....
2. Direct Query: Get Data / Advanced Option SQL WHERE - -
- a) is this the only way to limit the import data volume? (from within Power BI and not considering setting up Views in the SQL Server)
- b) would not the WHERE syntax potentially vary by brand/type database one connects to?
- c) is there a pro/con of instead implementing Views (or the equivalent) in the database itself, rather than PBI, IF that is possible to do?
3. Get Data imported data - and then with query editor one creates steps to delete rows/records based on some parameter (in example) - does the file size ultimately actually reduce? or is it just a filter and those records actually remain in the file...[this kind of is the same question as 2A].
thanks in advance
Hi CahabaData,
1. Power Query: isn't this a term specifically in excel? I see it used here alot but I don't think it is actually in PBI Desktop - but rather is Query Editor. true or no....? I should know this but am fumbling the ball here.....
Power Query is a self-service ETL (Extract, Transform, Load) tool which runs as an Excel add-in.
Power BI Desktop is a standalone application, which ties together Power Query, Power Pivot, and Power View in a standalone application, removing the Excel constraint. And Power BI Desktop is available for free.
2. Direct Query: Get Data / Advanced Option SQL WHERE - -
- a) is this the only way to limit the import data volume? (from within Power BI and not considering setting up Views in the SQL Server)
- b) would not the WHERE syntax potentially vary by brand/type database one connects to?
- c) is there a pro/con of instead implementing Views (or the equivalent) in the database itself, rather than PBI, IF that is possible to do?
a) As you have mentioned in question 3, the data can also be filtered in Query Editor.
b) Currently, we need to import the tables one by one, with running corresponding native database queries against the same database each time.
c) I don't think there is.
3. Get Data imported data - and then with query editor one creates steps to delete rows/records based on some parameter (in example) - does the file size ultimately actually reduce? or is it just a filter and those records actually remain in the file...[this kind of is the same question as 2A].
Based on my test, the size of pbix file will reduce after filtering records in query editor.
Regards
7 Replies
Hi CahabaData,
1. Power Query: isn't this a term specifically in excel? I see it used here alot but I don't think it is actually in PBI Desktop - but rather is Query Editor. true or no....? I should know this but am fumbling the ball here.....
Power Query is a self-service ETL (Extract, Transform, Load) tool which runs as an Excel add-in.
Power BI Desktop is a standalone application, which ties together Power Query, Power Pivot, and Power View in a standalone application, removing the Excel constraint. And Power BI Desktop is available for free.
2. Direct Query: Get Data / Advanced Option SQL WHERE - -
- a) is this the only way to limit the import data volume? (from within Power BI and not considering setting up Views in the SQL Server)
- b) would not the WHERE syntax potentially vary by brand/type database one connects to?
- c) is there a pro/con of instead implementing Views (or the equivalent) in the database itself, rather than PBI, IF that is possible to do?
a) As you have mentioned in question 3, the data can also be filtered in Query Editor.
b) Currently, we need to import the tables one by one, with running corresponding native database queries against the same database each time.
c) I don't think there is.
3. Get Data imported data - and then with query editor one creates steps to delete rows/records based on some parameter (in example) - does the file size ultimately actually reduce? or is it just a filter and those records actually remain in the file...[this kind of is the same question as 2A].
Based on my test, the size of pbix file will reduce after filtering records in query editor.
Regards
I appreciate your taking the time to reply to these high level questions.
My only quibble may be on 2C; if one is able to filter the data volume at the data source itself i.e. by creating Views - that is preferable than doing the modeling/filtering in Power BI because it would reduce the volume of data transfer which could be beneficial in some cases.
Thanks again
one more clarification question:
Direct Query: Get Data / Advanced Option SQL WHERE - -
** this method can limit the date imported rather than just a standard Get Data by defining the data set directly to the database
Is this possible to use this feature when one has multiple different DBs ? I think it is - but not sure.
Hi CahabaData,
Direct Query: Get Data / Advanced Option SQL WHERE - -
Is this possible to use this feature when one has multiple different DBs ? I think it is - but not sure.
With Direct Query? I am not sure I understand this scenario, as we can import only one DB in a single dataset with Direct Query mode. :smileyhappy:
Regards
