Exam 70-779 topic 1 question 52 discussion

Actual exam question from Microsoft's 70-779

Question #: 52
Topic #: 1

You import the data from two next files into a PowerPivot model to create two tables named Customers and Invoices. Each table contains a column named
CustomerID.
When you attempt to create a relationship between the Customers table and the Invoices table by using the CustomerID column from each table, the relationship cannot be created due to duplicate CustomerID values.
You need to ensure that you can create the relationship.
What should you do?

A. Add an index column to the Customers query
B. Add an index column to the Invoices query
C. Group the Customers query by CustomerID
D. Sort the Invoices query by CustomerID, and then add a Fill Down step

Show Suggested Answer

Suggested Answer: C 🗳️

by r8d1 at July 14, 2020, 12:22 p.m.

Comments

Submit Cancel

CDL

4 years, 8 months ago

Kinda same as previous Q. we need a dimension table to create a relationship, which contains unique identifier. just Group by, then "Max" operation in PQ editor, then Load to data model, refresh, Link to "Order" in diagram View in data Model.

upvoted 3 times

...

ddben

4 years, 10 months ago

grouping in Excel is a visual function but I don't think it relates to the content of the query, as such I think it should be answer B. Every invoice should have its own ID number and then the relationship can be built. This is also how it is done in practice. Invoices have indidual ID's that can be linked to a customer number. No two customer numbers are the same, no two invoice IDs are the same. but each invoice ID relates to a single customer number.

upvoted 1 times

...

r8d1

4 years, 11 months ago

Link demonstrates how to Group: https://www.educba.com/group-in-excel/

upvoted 1 times

PowerBIconsultant12

4 years, 8 months ago

This link is wrong. Ths is visual grouping in Excel sheets. There is another grouping in the query editor that create unique rows. Please see the following link: https://support.microsoft.com/en-us/office/group-rows-in-a-table-power-query-e1b9e916-6fcc-40bf-a6e8-ef928240adf1 Therefore C is definitely the answer

upvoted 2 times

...