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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.