Forum Discussion
displaying incorrect data
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"
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.
Thanks Sandip_Palit, that did not work. I applied those steps:
and visual still shows the same issue:
