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"
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?
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.