You have a table in Power BI Desktop as shown in the following exhibit. You pivot the columns as shown in the following exhibit. You need to resolve the error in row 3. The solution must preserve all the data. What should you do?
A.
Change the Data Type of the Value column.
B.
Select the Key column, and then click Remove Duplicates.
C.
Change the Aggregate Value Function of the pivot.
D.
Select the Score column, and then click Remove Errors.
I have tried all options, C is correct answer, B or D will remove some data, not satisfy constraint to keep all data. A, will cause error in column student, so choose C will count, not Sum the Value, but no more error.
You are correct the answer is C. You don't want to use count though for the aggregation.
Originally my M code was as follows:
= Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Key]), "Key", "Value")
When I change or actually provide an aggregation of List.Max, the error is removed and the highest score is kept, List.Min would also work giving you the lowest value and again no error. There is no command to click to do this I just edited the applied step in the function box.
= Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Key]), "Key", "Value", List.Max)
= Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Key]), "Key", "Value", List.Min)
https://docs.microsoft.com/en-us/powerquery-m/table-pivot
https://docs.microsoft.com/en-us/powerquery-m/list-max
https://docs.microsoft.com/en-us/powerquery-m/list-min
Tested, C is correct.
Edit in Query Editor.
1. [ID] as "whole number", [Key] as "Text", [Value] as "Text"
2. ribbon tab "Transform" -> select [Key], click "Pivot Column"
3. "Value Column" -> [Value]
4. Advanced options
if you choose "Don't Aggregate" --> "Error" pop up
to avoid this, just choose others as below
count(all), count(not blank), min, max, etc..
However in my original question I was given "Select the ID and Key column, and then click Remove Duplicates", without the ID portion I am not sure what I would select.
Would love some insight into how changing the aggregate value would be a solution?
"The solution must preserve all the data." If you remove it, you won't be able to preserve it.
upvoted 12 times
...
...
This section is not available anymore. Please use the main Exam Page.70-778 Exam Questions
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
Hien
Highly Voted 5 years, 10 months agorhsdeal
5 years, 10 months agoHien
5 years, 9 months ago150oche790
5 years, 1 month agoCDL
Most Recent 4 years, 7 months agosumitmalik
5 years agorhsdeal
5 years, 10 months agorhsdeal
5 years, 10 months agoBrunobsv
5 years, 6 months ago