Forum Discussion

elliejiang20's avatar
elliejiang20
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago
Solved

How to make Power BI desktop show data case-sensitive?

I have columnA with data AAA and aaa. They show in Power Query correct with case-sensitive: AAA and aaa under columnA, but load to Power BI desktop, only show AAA under columnA, so my slicer for columnA doesn't works to filter other data through columnA. How to reslove it?

Need Help
  • KNP's avatar
    KNP
    4 years ago

    The code in the last part of the article shows how to incorporate it into a query.

     

    let
  Source = SourceData,
  ToList = Table.AddColumn(Source, "Chars", each Text.ToList([OriginalText])),
  LowerCaseChars = {"a" .. "z"},
  AddInvisibleChars = Table.AddColumn(
    ToList,
    "AddInvisibleChars",
    each List.Transform(
      [Chars],
      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"})
in
  RemovedOtherColumns

     

    Basically, if you copy and pasted everything from 'ToList' to the end you could add that to the end of your query. All you would need to change is 'Source' and '[OriginalText]' in the 'ToList' line to match your previous step and column name with the lower case characters.

     

    I've attached the PBIX file from the blog post if you'd rather use that.

    If you need further help, please post the M code for the query you're trying to incorporate this in to.

     

5 Replies

    • bcdobbs's avatar
      bcdobbs
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Thanks for that link, I'd not seen that before!

  • elliejiang20's avatar
    elliejiang20
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    columnA is a column from big data table with a lots of values. I can't manually distingush which values are lower case value. How can I do?

  • elliejiang20's avatar
    elliejiang20
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    columnA is with a lots of values from a big table . Can't manually distingush which values are lower case in hard-code value as per artical above, am I right?

    • KNP's avatar
      KNP
      Icon for Super User rankSuper User
      4 years ago

      The code in the last part of the article shows how to incorporate it into a query.

       

      let
  Source = SourceData,
  ToList = Table.AddColumn(Source, "Chars", each Text.ToList([OriginalText])),
  LowerCaseChars = {"a" .. "z"},
  AddInvisibleChars = Table.AddColumn(
    ToList,
    "AddInvisibleChars",
    each List.Transform(
      [Chars],
      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"})
in
  RemovedOtherColumns

       

      Basically, if you copy and pasted everything from 'ToList' to the end you could add that to the end of your query. All you would need to change is 'Source' and '[OriginalText]' in the 'ToList' line to match your previous step and column name with the lower case characters.

       

      I've attached the PBIX file from the blog post if you'd rather use that.

      If you need further help, please post the M code for the query you're trying to incorporate this in to.