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

PL-300 By islever Editorial Last updated

Profile and clean the data

Short answer

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

Question

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?
  1. Yes Correct Answer
  2. No

Key Takeaways

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.

Common mistake: 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.

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

This link opens the official vendor documentation in a new tab.

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.

More PL-300 FAQ →

Community Votes

A
55%
B
45%

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

Discussion 15 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 15

  1. 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.
  2. 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
  3. NadeemSalam 👍 2 Answered 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.
  4. shreejoshi 👍 2 Answered B
    Adding index will not affect other column value or it's type. so error will remain the same.
  5. Andrew_17 👍 2
    Case sensitive data was resolved in data source
Show 10 more comments
  1. Pitxunet 👍 1 Answered 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.
  2. 7Veritas 👍 1 Answered 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
  3. 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
  4. 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.
  5. kbcity1 👍 1 Answered 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.
  6. BhavnishM 👍 1 Answered B
    The column contains case sensitive data doesn't means undefined errors can be fixed by resolving case
  7. f133b89 👍 1 Answered A
    Yes, this solution meets the goal.
  8. ae8a90c 👍 1 Answered A
    I believe the option A is correct
  9. dpkamalarani 👍 6
    A is right
  10. leela1507 👍 1 Answered A
    Answer A

Reader comments & corrections

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

Log in to comment, report an error, or add a note about this question.

PREVIOUSYEAR returns the full previous year, not the same day of last year

S-grade

Shrinking a Power BI model for monthly transaction totals with a star schema

S-grade

Northwind Traders semantic model: choosing Import storage mode for the Power BI tables

S-grade

Connecting Power BI Desktop to Azure DevOps Analytics views with the OData Feed connector

S-grade

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