displaying incorrect data

[TomSinAA]

I have a visual displaying data from a MS Access table.  The visual is switching the route field from what is in the MS Access table.

MS Access table source data:

#Route IdRoute No

32628	4165_Bham Ne 1_0730_M_W
32628	4165_Bham Ne 1_0730_M_W
32628	4165_Bham Ne 1_0730_M_W
32628	4165_Bham Ne 1_0730_M_W
32628	4165_Bham Ne 1_0730_M_W
32629	4165_Bham NE 1_0730_M_W
32629	4165_Bham NE 1_0730_M_W
32629	4165_Bham NE 1_0730_M_W
32629	4165_Bham NE 1_0730_M_W
32629	4165_Bham NE 1_0730_M_W

The visual is displaying the route with ID= 32628 as the Route No for ID=32629:

The difference is the lower case e in the "Ne" part of the route.  

[ryan_mayu]

@TomSinAA 

not clear about your question. I didn't see 32629 with Ne in your access data. Why it display in your powerbi screenshot?

[TomSinAA]

Hello ryan_mayu,  I believe @danextian  explained why it is happening: This is expected behavior. VertiPaq stores and indexes text in a normalized, case-insensitive way, so APPLE, apple, and Apple are treated as the same value, with only the first encountered version kept.

[TomSinAA]

Hello @danextian

Here is my query:

let
Source = Access.Database(File.Contents("path to database is here"), [CreateNavigationProperties=true]),
_tbl_StaticRouteDetail = Source{[Schema="",Item="tbl_StaticRouteDetail"]}[Data],
#"Filtered Rows" = Table.SelectRows(_tbl_StaticRouteDetail, each [Active] = "-1")
in
#"Filtered Rows"

I am not sure how to modify it to incorporate your code.  I tried a few things but was not successful

[v-agajavelly]

Hi @TomSinAA ,

Just checking if you tried the rank + zero-width space fix did it sort out the Route No display issue?

Regards,
Akhil.

[v-agajavelly]

Hi @TomSinAA ,

Thanks @danextian  your explanation about VertiPaq being case-insensitive and the rank + invisible character workaround makes perfect sense. This is exactly the kind of insight that clears up why the values were collapsing in my visual, and it’s a solid, working fix.  
@TomSinAA did you get a chance to look into danextian’s solution? It might be worth trying, as it handles the case-only differences in your Route No values and ensures they display correctly in the report. It should resolve the issue if followed step by step. Let us know how it goes and if it works.

Thanks,
Akhil.

[danextian · Accepted solution]

Hi @TomSinAA 

This is expected behavior. VertiPaq stores and indexes text in a normalized, case-insensitive way, so APPLE, apple, and Apple are treated as the same value, with only the first encountered version kept. While this improves compression and reduces model size, it also means you cannot store two values that differ only by letter casing. A possible workaround is to append an invisible character to the text, repeated according to a defined sort order, so that VertiPaq treats them as unique - you can create a rank column for that sort order (not available in the GUI).

M Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYyM7JQ0lEyMTQzjXfKSMxV8EtVMIw3MDc2iPeND1eK1RmcaixR1bgOajWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"#Route Id" = _t, #"Route No" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"#Route Id", Int64.Type}, {"Route No", type text}}),
    #"Added Route No Index" = Table.AddRankColumn(
   #"Changed Type",
   "Route No Index",
    {"Route No", Order.Ascending},
     [RankKind = RankKind.Dense]
),
    #"Added Custom" = Table.AddColumn(#"Added Route No Index", "Route No2", each [Route No] & Text.Repeat(Character.FromNumber(8203), [Route No Index]), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Route No"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Route No2", "Route No"}})
in
    #"Renamed Columns"

[TomSinAA]

Hi danextian,  that worked to make the values unique.  So here is code I used for that: 

let
Source = Access.Database(
File.Contents("C:\Users\SinskiT\OneDrive - American Red Cross\IDA\TMS\Reporting\PBI\TMS Reporting Config Data.accdb"),
[CreateNavigationProperties=true]
),
_tbl_StaticRouteDetail = Source{[Schema="",Item="tbl_StaticRouteDetail"]}[Data],

// Filter only active rows
#"Filtered Rows" = Table.SelectRows(_tbl_StaticRouteDetail, each [Active] = "-1"),

// Add dense rank index based on Route No
#"Added Route No Index" = Table.AddRankColumn(
#"Filtered Rows",
"Route No Index",
{"Route No", Order.Ascending},
[RankKind = RankKind.Dense]
),

// Add invisible characters to make Route No unique
#"Added Custom" = Table.AddColumn(
#"Added Route No Index",
"Route No2",
each [Route No] & Text.Repeat(Character.FromNumber(8203), [Route No Index]),
type text
),

// Replace original Route No with modified version
#"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Route No"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns", {{"Route No2", "Route No"}})
in
#"Renamed Columns"

However, the invisible character causes issues when working with the data outside of PowerBI. For example, if the data in Power BI is exported or if the value is copied it includes the invisible character which make further analysis problematice.  For example, if the data is exported or copied to Excel the value generates error when using formulas such as Exact when comparing it to our source data for QC purposes.  Is there another way that would not add an invisible character to address the issue?

[danextian]

Is there another way that would not add an invisible character to address the issue? -- Well, you can add a repeated characters instead which won't be desirable. 

Either you create a macro to remove those in Excel or tell your users to remove them with a formula prior to doing a comparison

=SUBSTITUTE(C2,UNICHAR(8203),"")

Replace the range with the actual one.

[Sandip_Palit]

This is a one-time setup in the Power Query Editor. Once done, your data will always refresh correctly.

Open Power Query: In Power BI Desktop, go to the Home tab and click Transform data.

Add a Custom Column: Select your query from the list on the left. Then, go to the Add Column tab on the ribbon and click Custom Column.

Enter the Formula: In the Custom Column window, enter the following formula. This appends a "zero-width space" character to the end of each Route No. This character is invisible in your report but makes each string unique to the data model.

New column name: Route No Fixed

Custom column formula:

[Route No] & Character.FromNumber(8203)

Replace the Old Column:

You no longer need the original Route No column. Right-click its header and select Remove.

Right-click the header of your new Route No Fixed column, select Rename, and name it Route No.

Close & Apply: Click the Close & Apply button on the Home tab to load the changes into your data model.

Your visual should now display the correct Route No for each #Route Id because Power BI will no longer be able to group the case-different values together.

If this explanation and solution resolve your issue, please like and accept the solution.

[TomSinAA]

Thanks Sandip_Palit, that did not work.  I applied those steps:

and visual still shows the same issue: