Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Manjula
Helper I
Helper I

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

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.

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1689574783836.png

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:

vyueyunzhmsft_1-1689574827713.png

 

 

 

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

View solution in original post

3 REPLIES 3
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1689574783836.png

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:

vyueyunzhmsft_1-1689574827713.png

 

 

 

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
Super User
Super User

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
Super User
Super User

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.