Case Sensitivity in the Power Front end and Character Encoding under the bonnet getting misaligned

[Anonymous]

As outlined in Chris Webb's post on Case Sensivity being treated differently in the native data source , power query and the Power BI front end . Post below for anyone not familar . 

Chris Webb's BI Blog: Power BI And Case Sensitivity Chris Webb's BI Blog (crossjoin.co.uk)

Figure 1- An Example of the Case Sensivity issue . ( Back End Vs Front End )

So for a Customer with an Customer Order Reference Number I may have z137 and Z137 which mean on the power BI front end we get 2 entries showing z137 in the Customer Order Reference Number column, without the above fix, instead or one showing z137 and one showing Z137.

Customer Name	Customer Order Reference Number
Janice Industries	z137
Janice Industries	z137 ( should show as Z137 ! )

When I apply Chris Webb Fix like so on this field in Power Query using the below code , for the above I get one row with z?137 for one and Z137 for the other .  I then delete the ? in the z?137 and then it breaks the case sensitity fix. My days.

Code Sample 1 

#"Replaced Value3" = Table.ReplaceValue(#"Changed Type1",null,"",Replacer.ReplaceValue,{"cornumber"}),

    ToList = Table.AddColumn(

        #"Replaced Value3",

        "Chars",

        each Text.ToList([cornumber])

        ),

    LowerCaseChars =

    {"a".."z"},

    AddInvisibleChars =

    Table.AddColumn(

        ToList,

        "AddInvisibleChars",

        each

        List.Transform(

            [Chars],

            each

            if

            List.Contains(LowerCaseChars, _)

            then

            _ & Character.FromNumber(8023)

            else _

            )

            ),

    RecombineList =

    Table.AddColumn(AddInvisibleChars, "COR NUMBER FIXED", each Text.Combine([AddInvisibleChars]), type text),

Please note that before this transform I do this and afterwards as well ?

Code Sample 2

= Table.TransformColumns(
    #"Replaced Value1",
    {
     "cornumber" ,
     each if (_ = null or _ = "") then null
          else Text.FromBinary(Text.ToBinary(_,1251), TextEncoding.UTF8)
    }
  )

So the question in Power query is why do the numbers come out like this ?

For a COR number POtest it becomes “Pot?e?s?t?”. Any ideas what I am doing wrong ?

Am I putting in the zero width character space from Chris Webb’s fix wrongly using that code  ?

Figure 2 - Character encoding for HTML character 8023

[v-henryk-mstf]

Hi @Anonymous ,

Thank you for your feedback. If the problem has been solved, you can mark the correct response as the standard answer to help the other members find it more quickly.

Looking forward to your reply.

Best Regards,
Henry

[v-henryk-mstf]

Hi @Anonymous ,

No errors seem to be found in the code, can you provide more information. Looking forward to your reply.

Best Regards,
Henry

[Anonymous · Accepted solution]

Using your vim editor on the PO Number field at the Power BI stage. This is what you see .

The characters are needed by the Power BI Front end but with the Power App and SharePoint they are not required . The solution in the end is to remove them in Azure Data Factory using sed