Rows are missing in Power BI model where rows are present in Query

[Manjula]

Hi , 

I have a issue while loading data, I have imported few Merchants from database, in Power Query all the source merchants id's are showing,but when i check in power bi data model few merchants are missing

source data

Merchant:
ABS
FLY
Dora
MyCar
mycar

Power Query also has the same values , we have two MyCar and mycar, i need both but mycar is missing in model 

Power Model:

Merchant:
ABS
FLY
Dora
MyCar

Any idea why rows are missing , one more thing if i keep filter in query editor on mycar then i can see rows in model.

[v-yueyunzh-msft · Accepted solution]

Hi, @Manjula 

Case sensitivity is one of the more confusing aspects of Power BI: while the Power Query engine is case sensitive, the main Power BI engine (that means datasets, relationships, DAX etc) is case insensitive.

For this situation, we can use M code to  make it text insensitive.

Here are the steps you can refer to :
(1)The test data is the same as yours.

(2)We can put this M code in the "Advanced Editor" in Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQKVorViVZy84kE0y75RYlghm+lc2IRmJVbmQxixQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Merchant = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Merchant", type text}}),
    Custom1 = Table.AddColumn(
        #"Changed Type", 
        "Chars", 
        each Text.ToList([Merchant])
        ),
    LowerCaseChars = {"a".."z"},
    Custom2 = Table.AddColumn(
        Custom1, 
        "AddInvisibleChars", 
        each 
        List.Transform(
            [Chars], 
            each 
            if 
            List.Contains(LowerCaseChars, _) 
            then 
            _ & Character.FromNumber(8203) 
            else _
            )
            ),
    Custom3 = Table.AddColumn(
        Custom2, 
        "OutputText", 
        each 
        Text.Combine([AddInvisibleChars]), 
        type text
        ),
    #"Removed Columns" = Table.RemoveColumns(Custom3,{"Merchant", "Chars", "AddInvisibleChars"})
in
    #"Removed Columns"

Then we can apply the data to Desktop and we can put this field on the visual , the result as follows:

For this , you can also refer to this :
Power BI And Case Sensitivity - Chris Webb's BI Blog (crossjoin.co.uk)

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[johnt75]

In Power BI Desktop all values are case insensitive, unlike in Power Query, so MyCar and mycar will be seen as the same thing.

[Idrissshatila]

Hello @Manjula ,

Then the missing merchants are related to the excluded filter, so you need to get both of them or replace one of them to show as the other.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin