Handling Case Sensitivity in Power Query and Power BI Desktop

[Balakrishnan_J]

Hi Power BI Community Members,

Hope all are doing well !!!

I am facing issue in handling case sensitivity in Power Query and Power BI Desktop.

Issue Explanation:
I am having a table in power query as below

Output Explanation:
I want to load this table to Power BI Desktop as it is in Power Query but when I try to load data it is showing as below

Kindly give me solution to handle this scenario. Attached M-Code below for your reference.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLLL1EIzy/KzsxLV4rViVYyAorlAcXKkcSMQer8QxTC/YO8Pf3cwWImYL0KxZnpeYk5YBFTsE5kETOwPoVgT3c/Rx+l2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [S.No = _t, Comments = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"S.No", Int64.Type}, {"Comments", type text}})
in
#"Changed Type"

[Balakrishnan_J]

Hi Power BI Community Members,

Hope all are doing well !!!
Big Thanks for providing your valuable time to work on this issue.

Hi @jaineshp  & @v-saisrao-msft 

I want to retain my values in Comments Column as it is in power query but here in DisplayComment Column binary values are added in the end.
I need to use this column in slicer visual of Power BI Desktop.

Hi @burakkaragoz 

Showing error like this when I tried to use your query.

Kindly provide another solution to handle this issue.

[v-saisrao-msft · Accepted solution]

Hi @Balakrishnan_J,

Below the the updated M-query and pbix file attached for your reference:

Hope this helps

Thank you.

[jaineshp]

Hi @Balakrishnan_J ,

You're trying to retain case-sensitive values in the Comments column exactly as they appear in Power Query, and want to use this field in a slicer visual without adding any binary suffixes or losing distinct casing.

Clean and Working Solution (No Binary, Fully Slicer-Compatible)

Steps in Power Query:

1. Add an Index Column to ensure uniqueness:

     #"Added Index" = Table.AddIndexColumn(#"Changed Type", "CommentKey", 1, 1, Int64.Type)

     2. (Optional but cleaner) Rename Comments to DisplayComment to clearly separate display from logic.

Final structure:

• DisplayComment → Your original text, casing preserved

• CommentKey → Unique value for grouping/filtering in visuals

Why This Works:

Power BI's data model is case-insensitive — so “Test” and “test” are treated the same in slicers and visuals. This solution avoids binary hacks and instead introduces a hidden unique key to retain distinct rows and allow clean slicer display using DisplayComment.

How to Use in Power BI:

• Use DisplayComment in slicers or table visuals.

• If needed, use CommentKey as a hidden grouping field in DAX logic or relationships.

Let me know if you need help setting up visuals or DAX with this!

Best regards,
Jainesh Poojara | Power BI Developer

[jaineshp]

Hi @Balakrishnan_J,

Power Query shows text with different casing (e.g., "Not Working", "not working"), but Power BI groups them as one in visuals — because the data model is case-insensitive.

Why This Happens

Power BI’s internal engine (VertiPaq) ignores case in text values when loading data.

Quick Solutions

SolutionWhat to DoPurpose

1. Keep S.No in Visual	Include S.No or unique column in table visual	Prevents automatic grouping
2. Add Custom Column	In Power Query: Comments & " - " & Text.ToBinary(Comments)	Keeps case distinction
3. Display vs Group Columns	Create: DisplayName = Comments, GroupName = Text.Lower(Comments)	Group logically, display original case

Recommended

If you only need to display all rows distinctly, just include S.No in your table visual.

For more control, use the Display vs Group column approach.

Best Regards,
Jainesh Poojara | Power BI Developer

[v-saisrao-msft]

Hi @Balakrishnan_J,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Below is the M-Query used and attached the pbix file for your reference:

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText("i45WMlTSUfLLL1EIzy/KzsxLV4rViVYyAorlAcXKkcSMQer8QxTC/YO8Pf3cwWImYL0KxZnpeYk5YBFTsE5kETOwPoVgT3c/Rx+l2FgA", BinaryEncoding.Base64),
                Compression.Deflate)
        ),
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [S.No = _t, Comments = _t]
    ),
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"S.No", Int64.Type}, {"Comments", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "CaseHash", each Text.ToBinary([Comments])),

    #"Converted CaseHash to Text" = Table.TransformColumns(#"Added Custom", {{"CaseHash", each Binary.ToText(_, BinaryEncoding.Base64)}}),

    #"Added DisplayComment" = Table.AddColumn(#"Converted CaseHash to Text", "DisplayComment", each [Comments] & " (" & [CaseHash] & ")")

in
    #"Added DisplayComment"

Hope this helps
Thank you.

[burakkaragoz]

Hi @Balakrishnan_J ,

Power BI is doing this because it treats text values that only differ by case as the same thing. So "Not Working", "not working", and "NOT WORKING" all get lumped together.

Easy fixes:

Add row numbers to make them unique: In Power Query, add a custom column:

= Table.AddColumn(#"Changed Type", "UniqueComments", 
    each [Comments] & " (" & Text.From([S.No]) & ")")

Use this new column in your visuals instead. Since each one has a different number, Power BI won't group them.

Invisible character trick:

= Table.TransformColumns(#"Changed Type", 
    {{"Comments", each _ & UNICHAR(8203) & Text.From([S.No])}})

This adds an invisible character plus the row number, so they look the same but are technically different.

Model-level fix: Create a calculated column in Power BI:

UniqueComments = [Comments] & " [" & [S.No] & "]"

Best approach: Go with the first one - it's clearest for users to understand what's happening with the case differences.

This is just how Power BI works - it's not really designed to handle case-sensitive text distinctions well in visuals.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.