Query Terminology & General Ideas

[CahabaData]

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

[v-ljerr-msft · Accepted solution]

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

[CahabaData]

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.

[v-ljerr-msft]

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. 

Regards

[CahabaData]

perhaps the confusion lies in the defintion of 'dataset'....

in a single PBIX file one is attempting to mash together data from differing databases.

so the need is to import/query tables from differing databases individually

inside PBI the tables from differing DBs would continue to be separate tables

- - - this is based on my current thinking (potentially wrong) that Direct Query still imports data into the PBIX file just like a generic Get Data, only the candidate data from the source is more narrowly defined by the DQ's WHERE statement.......

so a single DQ set up would be of a single DB...but can there be a 2nd or 3rd DQ set up each from differing DBs ?

[v-ljerr-msft]

Hi @CahabaData,

---

- - - this is based on my current thinking (potentially wrong) that Direct Query still imports data into the PBIX file just like a generic Get Data, only the candidate data from the source is more narrowly defined by the DQ's WHERE statement.......

According to this article: DirectQuery – no data is imported or copied into Power BI Desktop. The selected tables and columns appear in the Fields list. As you create or interact with a visualization, Power BI Desktop queries the underlying data source(Based on my understanding, using DQ's WHERE statement will make the queries running against a narrowed DB, instead of the whole one), which means you’re always viewing current data. 

Regards

[CahabaData]

thanks for that - so in PBI Desktop where using DQ - then I suppose one can't move/copy/share that pbix file if it doesn't actually import any data (yes of course the link is not longer valid - but just in terms of potentially sharing a report file).......interesting

If one sets up a DQ to DB1 and gets that going.... then can the developer next set up a second DQ to DB2...and so on....  mashing data from different sources... ?

[CahabaData]

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