Forum Discussion

Junaid11's avatar
Junaid11
Helper V
4 years ago
Solved

Special Character in Column Name

Hello,

I have few columns which have special characters in them. I tried creating a custom column based on column with special character in its name but it did not allow me to do that in Power query. My code was below:

Text.Combine( List.Distinct( List.Transform([Check][Work?], Text.Trim)),",")

I cannot change the column name. Is there a way that I can use this column name of Work? with special character. Help would be highly appreciated.

Thank you 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    [#"Work?"] instead of [Work?] will work.

     

    --Nate

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    [#"Work?"] instead of [Work?] will work.

     

    --Nate

    • King7son1's avatar
      King7son1
      Frequent Visitor
      1 year ago
      Kudos  to Mark from Excel off the grid https://www.youtube.com/watch?v=P3lTPggW2yc
       
      you can create a function to clean up special characters from column names like this
      In my case I had a main table with nexted tables that i wanted to clean up before combining so you called my function by 
      Table.TransformColumns(Source,{"Data", each Table.TransformColumnNames(_,CleanHeaders)})
       
       // Custom function to replace special characters and spaces with an underscore
      let
          ReplaceSpecialCharacters = (inputText as text) as text =>
          let
              // Define the characters to replace
              specialCharacters = {".", ",", "!", "@", "#", "$", "%", "^", "&", "*", "(", ")", "-", "_", "=", "+", "[", "]", "{", "}", "|", "\", ":", ";", "'", "<", ">", "?", "/"," " },
              // Replace each special character with an underscore
              replaceUnderscore = List.Accumulate(specialCharacters, inputText, (state, current) => Text.Replace(state, current, "_"))
          in
              replaceUnderscore
      in
          ReplaceSpecialCharacters
       
  • Danalber's avatar
    Danalber
    Advocate II
    2 years ago

    Hi,

    I had a similar issue today and the solution above didn't work. After many try and errors the solution in my case was very easy.

    In the power query the column name with the special character, "#" in my case, just needed to be set in a double quote.

     

    SELECT TRANSACTION_ID, TRANSACTION_TYPE, ""COMMENTS#1"" FROM PROD_DB.PO.RCV_TRANSACTIONS

     

    The single quote '' and squared brakes [] both didn't work in the power query.

    Hope this might be useful to someone at some point. Wasted quite a bit time today. 🙂

    BR