Adding an index key and normalizing casing to resolve DirectQuery undefined values

Profile and clean the data

Answer Correct answer: A — Yes, an index key plus casing normalization in the data source is the second documented remedy for this DirectQuery case-sensitivity problem.

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 data source that contains a column. The column contains case sensitive data. You have a Power BI semantic model in DirectQuery mode. You connect to the model and discover that it contains undefined values and errors. You need to resolve the issue. Solution: You add an index key and normalize casing in the data source. Does this meet the goal?

Yes Correct Answer
No

directquery case sensitive undefined values power bi case sensitive errors add index key normalize casing pl-300 profile clean data

Community Votes

55%

45%

55% of anonymous learners picked answer A. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

Adding an index key plus normalizing casing in the data source is the second documented remedy for undefined values caused by case-sensitive DirectQuery sources — the index key preserves row identity for values that look identical once casing is flattened.

For a case-sensitive column feeding a case-insensitive DirectQuery model that shows undefined values and errors, the proposed solution adds an index key and normalizes casing in the data source.

Reasoning that an index key alone does not fix case collisions — true by itself, but the solution pairs it with casing normalization in the data source, which is precisely the combination the official note recommends.

Community Discussion (15 comments)

Tex02 👍 11

I think the answer is A. adding the index key won't fix the problem by itself but you are also normalising the case which will resolve it.

dpkamalarani 👍 6

A is right

BhavnishM 👍 1 Selected: B

The column contains case sensitive data doesn't means undefined errors can be fixed by resolving case

NadeemSalam 👍 2 Selected: A

Yes, is the right answer. Adding an index key (e.g., a unique identifier) ensures that each row in the data source is uniquely identifiable, even if the text values are the same after normalizing casing. This helps maintain data integrity and avoids ambiguity in the semantic model. By normalizing the casing (e.g., converting all text to uppercase or lowercase) in the data source, you eliminate case sensitivity issues. This ensures that values like "Apple" and "apple" are treated as the same, resolving undefined values and errors in the Power BI semantic model. Since the semantic model is in DirectQuery mode, all transformations and data preparation must be handled at the data source level. Normalizing casing and adding an index key in the data source ensures that Power BI receives consistent and unambiguous data, resolving the issue without requiring transformations in Power BI.

Pitxunet 👍 1 Selected: A

The answer is "Yes". I found the following in the Microsoft documentation: "Because the engine that stores and queries data in Power BI is case insensitive, take special care when you work in DirectQuery mode with a case-sensitive source. Power BI assumes that the source has eliminated duplicate rows. Because Power BI is case insensitive, it treats two values that differ only by case as duplicate, whereas the source might not treat them as such. In such cases, the final result is undefined. To avoid this situation, if you use DirectQuery mode with a case-sensitive data source, normalize casing in the source query or in Power Query Editor." Source: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types Therefore, the solution is to normalize casing at the source.

kbcity1 👍 1 Selected: B

The answer is B, adding an index key and normalizing casing in the data source does not resolve the issue of undefined values and errors in the Power BI semantic model in DirectQuery mode.

f133b89 👍 1 Selected: A

Yes, this solution meets the goal.

leela1507 👍 1 Selected: A

Answer A

7Veritas 👍 1 Selected: B

Adding Index does not resolve the question, it is an unnecessary complication, hence the solution is B. While normalizing casing in the data source would help address case-sensitivity issues, adding an index key is unnecessary and doesn't contribute to solving the specific problem of undefined values and errors caused by case-sensitive data. Adding an index key: - Doesn't address case sensitivity issues - Adds unnecessary complexity - Is not relevant to resolving undefined values and errors caused by case-sensitive data - Creates additional overhead without solving the core problem

shreejoshi 👍 2 Selected: B

Adding index will not affect other column value or it's type. so error will remain the same.

Andrew_17 👍 2

Case sensitive data was resolved in data source

Enuoma 👍 1

Answer is A. Yes, adding an index key can help resolve issues related to undefined values or error. Normalizing casing ensures that case-sensitive data is consistent reducing chance of mismatches or errors

hsetin 👍 1

aCCORDING TO PERPLEXITY, the answer is still NO, as this does not solve the issue of undefined values and errors, and also direct query does not really work well with normalizing teh case either.

ae8a90c 👍 1 Selected: A

I believe the option A is correct

leah.pring 👍 4

According to Copilot, the answer is A. Here is the explanation I got: Yes, adding an index key and normalizing the casing in the data source can help resolve the issue of undefined values and errors in a Power BI semantic model in DirectQuery mode. Here’s why: Index Key: Adding an index key can improve query performance and ensure that each row is uniquely identifiable, which helps in avoiding errors related to duplicate or missing data1. Normalizing Casing: Normalizing the casing ensures consistency in the data, which can prevent mismatches and errors that arise from case sensitivity issues1. These steps should help in cleaning up the data and making it more reliable for analysis in Power BI. https://learn.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Expert Analysis

Why the Answer Is Correct

Microsoft's data types documentation describes the same case-insensitivity issue as the scenario: DirectQuery over a case-sensitive source can produce undefined values and errors, and one recommended fix is to add an index key and normalize the casing in the data source. The index key guarantees each row stays uniquely identifiable even after casing is normalized, so values that look identical to the case-insensitive engine no longer collapse into undefined results. The solution therefore meets the goal.

Why the Other Options Are Wrong

The opposing answer (No) treats the index key as a red herring, but the documented remedy explicitly includes both parts together; neither part alone is claimed to be sufficient, and the question proposes exactly the documented pair. There is no requirement in the scenario that the fix stay confined to Power Query — the solution is allowed to change the data source, and doing so does not violate any stated constraint.

Community Comment Notes

Multiple commenters support Yes, one quoting the Microsoft documentation line about the case-insensitive engine requiring special care in DirectQuery over case-sensitive sources. The dissenting comments assert that case normalization cannot fix undefined errors, which overlooks that the official note pairs it with the index key for exactly this purpose.

Official Reference

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types

Exam Strategy

In DirectQuery scenario questions, scan for the paired fix: an index key plus casing normalization for case-sensitive source data. If a proposed solution includes both, treat it as the documented remedy for undefined values and errors and answer Yes; if it offers only one part or a Power Query-only change, suspect a trap. Use the Microsoft data types note as your decision rule, not whether the fix stays inside Power Query.

Frequently Asked Questions

Why do an index key and casing normalization fix undefined values in DirectQuery?

The index key keeps each row uniquely identifiable after casing is normalized, so the case-insensitive DirectQuery engine no longer collapses case-sensitive values into undefined results or errors.

Can normalizing casing alone resolve the errors with case-sensitive data?

No. The documented remedy pairs casing normalization with an index key; neither part alone is sufficient for DirectQuery over a case-sensitive source.

Does the fix need to stay inside Power Query instead of changing the data source?

No, the scenario permits changing the data source. Adding the index key and normalizing casing there matches the official Microsoft guidance for this issue.

Related Analysis

Practice All PL-300 Questions

Access 116 questions with complete answers and detailed explanations.

View Full PL-300 Practice Test →

← Back to PL-300 Study Guide