Exam DA-100 All Questions

View all questions & answers for the DA-100 exam

Exam DA-100 topic 1 question 18 discussion

Actual exam question from Microsoft's DA-100

Question #: 18
Topic #: 1

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table named Order that has more than 100 million records.
During the development process, you need to import a sample of the data from the Order table.
Solution: From Power Query Editor, you import the table and then add a filter step to the query.
Does this meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: B 🗳️

by Niz0 at Jan. 5, 2021, 9:48 a.m.

Comments

Submit Cancel

SPG

Highly Voted 4 years, 5 months ago

I think it's A because of query folding: the source is SQL Server, so with all steps applied a query will be generated and passed to the server (including the filter), so the result would be the same than including the WHERE clause in the query

upvoted 79 times

KmBhuvi

4 years ago

check the solution which is provided here and you will come to know why 'B' is correct

upvoted 5 times

...

kalyhot

4 years, 3 months ago

Agree with A. In this case the source is Sql server. ----------------------------------------------------------------- Tested. if you "Right Click" on the filter step, and you see "view Native Query", and when you see the code: """"""""""""""""""""""""""""""""""""""""""" select [_].[NOM], [_].[PRENOM], [_].[age] from [dbo].[employee] as [_] where [_].[NOM] = 'toto ' and [_].[NOM] is not null """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" the filter is added in the Native Query ==> so it will work ==> A

upvoted 16 times

...

eurekamike

3 years, 10 months ago

Correct. The main source of confusion is that is says import THEN filter. The import part refers to the first step in the query which is specifying the source. It's talking about retrieving the data and importing it into the query mashup engine, not power BI. Import means Select. Very poor choice of words on their part. Then we add the filter function as another step. Since filter is supported by query folding, the query engine transforms the query steps into a single query. Then it loads the data into PBI. Key thing to look for is that it asks you to put the filter in the same query that is importing (AKA Selecting) the data.

upvoted 12 times

eurekamike

3 years, 10 months ago

By the way this results in the same result as the other version of this question asking if adding a where clause to the SQL statement works. It does, query folding produces the same SQL statement.

upvoted 4 times

...

MayurD

Highly Voted 4 years, 2 months ago

The answer is B. Reason: The solution mentions "you import the table AND THEN add a filter". When you import the table, it imports all data not the sample of data.

upvoted 59 times

pedrohap

3 years, 12 months ago

But when you import the table, it is still possible for the query folding to happen. Therefore, the query sent to the database will contain the WHERE statement, bringing the filtered data instead of the full table

upvoted 12 times

...

iccent2

Most Recent 2 years, 9 months ago

Which is the correct answer now between A and B?

upvoted 2 times

...

Gecig

2 years, 10 months ago

Even though the given condition works appropriately because of Query Folding. But I think this isn't correct for the context of getting a sample. Because if someone specifically clicks on the 'Source' step of the query, the they will be able to see all the data since query folding happens at the 2nd step only. But with WHERE at the 1st step itself, would mean no additional data is seen. And hence a true sample.

upvoted 4 times

ismailqtr

2 years, 9 months ago

I agree with you, I think people are overthinking the question (not saying its wrong) but outside of the question scope. query folding is something that you have to setup as an additional step assuming Power Query is able to generate single query statements

upvoted 1 times

...

Mazarakis

2 years, 11 months ago

correct answer is A. Import Data, then when you are in power query editor , applied one more step, which is a filter. So we say to power bi , that when you are going to load the data to power bi desktop, you must do some steps on that data. So in this case we do not load all the data , but a party of these.

upvoted 2 times

...

bbshu0801

2 years, 11 months ago

Selected Answer: B

I would go for B because "then add a filter step to the query" is mentioned in question.

upvoted 2 times

...

Mazarakis

2 years, 11 months ago

Correct Answer is A. From power query editor --> New Source --> SQL server and choose import. Then inside the power query editor we go to a certain column and add a filter . So when we do apply the loaded data is a sample. We can try and compare the size of the power bi file, which will be smaller with this process. If we do the same thing without the filter step the size of power bi file will be bigger.

upvoted 1 times

...