Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

PowerQuery case sensitive workaround

Hi, 

 

I'm trying to use a workaround for PowerQuery being case insensitive but had a bit of trouble implementing it. The workaround adds an invisible character next to each lowercase character. https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/

 

In the example, a new table is made from the source data. I would like to just add a new column to my current table that copies my Street Order ID and adds in the invisible characters. Here's the code I have so far:

 

ToList = Table.AddColumn(Source,
"CaseSensitiveStreetID",
each Text.ToList([Street Order ID])
),
LowerCaseChars = {"a".."z"},
AddInvisibleChars =
Table.AddColumn(
ToList,
"AddInvisibleChars",
each
List.Transform(
[CaseSensitiveStreetID],
each
if
List.Contains(LowerCaseChars, _)
then
_ & Character.FromNumber(8203)
else _
)
),
RecombineList =
Table.AddColumn(
AddInvisibleChars,
"OutputText",
each
Text.Combine([AddInvisibleChars]),
type text
),

RemovedOtherColumns =
Table.SelectColumns(
RecombineList,
{"OutputText"}
),

 

However, this does not add a new column 'OutputText' to my table. What am I missing?

 

 

 

 

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Xue Ding,

     

    I found a workaround by concattening two ID numbers which gave me another UID. 

     

    Thanks

3 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support

    Hi Anonymous ,

    I test the code. They work fine in my sample. Can you please share some screenshots? Then we will understand clearly about your problem.

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi Xue Ding,

       

      I found a workaround by concattening two ID numbers which gave me another UID. 

       

      Thanks

      • v-xuding-msft's avatar
        v-xuding-msft
        Community Support

        Hi Anonymous ,

        Glad that you have resolved it. Please accept your answer as a solution. Then we will be able to close the thread. More people will benefit here.

         

        Best Regards,

        Xue Ding

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.