Exam 70-778 All Questions

View all questions & answers for the 70-778 exam

Exam 70-778 topic 1 question 79 discussion

Actual exam question from Microsoft's 70-778

Question #: 79
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 have a query for a table named Sales. Sales has a column named CustomerID. The Data Type of CustomerID is Whole Number.
You refresh the data and find several errors. You discover that new entries in the Sales table contain nonnumeric values.
You need to ensure that nonnumeric values in the CustomerID column are set to 0.
Solution: From Query Editor, open Advanced Editor and add the following query step.

Does this meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: B 🗳️

by TiaanR at July 10, 2019, 4:17 a.m.

Comments

Submit Cancel

AnetaK

Highly Voted 5 years, 6 months ago

I've replaced Errors with 0 with the Replace Errors option and that is what I've found in the Advanced Editor. #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"id", 0}}) It is exactly the same code as in the question. So the answer should be YES.

upvoted 14 times

...

TiaanR

Highly Voted 5 years, 11 months ago

That should work. No idea why No has been marked as correct.

upvoted 10 times

FrdFrd

4 years, 5 months ago

The formula works indeed. A is the correct answer

upvoted 2 times

...

nemanjaandic

Most Recent 4 years, 4 months ago

There are 3 reason why answer should be "No": 1) main reason, we also need to change last row of M statement - to say "in #"Replaced Errors"", without it it will not work 2) what if previous step is not called "Changed Type" but "Removed Column" 3) we also need to add comma sign before this row

upvoted 1 times

...

Lhouss

4 years, 6 months ago

Should be A.

upvoted 1 times

...

kyliek7

4 years, 7 months ago

Depend how tricky it is :D because if we assume that there are some steps there already the we need to add not only this line but also finishing one: in #"Replaced Errors" instead previous last step

upvoted 1 times

...

CDL

4 years, 7 months ago

Guys, I just tested, Code is right, so A is correct. step-1, import data into PQ editor; step-2, change the [CustomerID] to data type "whole number", and then "text form" will show "Error" step-3, replace "Error" with "0". (I think #"Changed Type" in the 1st arguement is indicating "Replacement Step" following the step of data type changing. Code as below: let Source = Excel.CurrentWorkbook(){[Name="customerID"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", Int64.Type}}), #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"CustomerID", 0}}) in #"Replaced Errors"

upvoted 3 times

...

FakherThabti

4 years, 9 months ago

53/5000 the query is well formed therefore answer A is correct

upvoted 1 times

...

Chocho

4 years, 10 months ago

it's not exactly the same. you need to add these : #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Customer Key", 0}}) in #"Replaced Errors"

upvoted 2 times

...

Raquel21

4 years, 10 months ago

0 should have " ", right?

upvoted 1 times

...

AmandaW

4 years, 11 months ago

Guys, I tried this code and it works perfectly. Then I know the reason why the answer is NO. Maybe because the last number is not zero it is a capital letter O? Or maybe only this code is not enough. You need to change the last step name after "in".

upvoted 2 times

borut

4 years, 10 months ago

I agree with you. If you just add query step, nothing will happen. You must change step name in "in" section of code. There must be a trick. If you use Replace Errors in UI, the step in "in" section is automatically changed to last step in code.

upvoted 2 times

...

p_eloy

4 years, 11 months ago

I believe the code is correct but the answer is B (No) because you need to add the code and specify that the 'in' argument retrieves the last command line, not the previous step that was showing on.

upvoted 1 times

...

pbia

4 years, 11 months ago

Just need to replace in parts by #"Replaced Errors " So not sure what should be answer, just replacing the given code won't work because in part (display part/result part) is not changed.

upvoted 1 times

...

Echicken69

5 years ago

so I tried this...and it worked?? let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYu5DcAwDAN3UZ1Gpr427xKG918jipUAAVgcD2TvZCpgNAbTWDopJIPJUkV/5cNwyFoHQSaVylbCGyt4ryk8zM3jeMcm56TU8Xi7aIwb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", type text}}), #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"CustomerID", Int64.Type}}), #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type",{{"CustomerID",0}}) in #"Replaced Errors" So I'm super confused....How specific are we going here? Does the Answer have to contain the "comma" or is it assumed that the comma will be added when you enter in a new Query step?

upvoted 2 times

...

Devdatta

5 years ago

I believe this does not work because that is not the only change in Advanced Editor. Once you add this step for ReplaceError, you need to also replace the "in" part to mention this new step instead of old.

upvoted 3 times

...

Agustin

5 years ago

Tested... This will normally work if you have a previous step named "Changed Type". However, you may have renamed that step as "ChangedToWholeNumber". As a result, there is no guarantee it will work. So... A or B?? I do not know what they would expect us to answer in these cases.

upvoted 3 times

...

Leonardo_84

5 years, 1 month ago

#"Changed Type" is the problem... This could help: https://datachant.com/2017/01/11/10-common-mistakes-powerbi-powerquery-pitfall-2/

upvoted 1 times

...

Rathish

5 years, 1 month ago

"Replaced Error"= Table.ReplaceErrorValues(#"Changed Type", {{"CustomerID", 0}}) This is the value from my testing. I could not see any difference. Let me know, if you could see some change here.

upvoted 1 times

...

Load full discussion...