Special Character in Column Name

[Junaid11]

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 

[Danalber]

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

[Anonymous · Accepted solution]

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

--Nate

[King7son1]

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