Using the "IF" function to create a new column

Ah, OK, sounds to me like you want more of a "lookup" function. See below code for creating one of these. Note that while I use a manually created list, you could use a list created from something like a database table, etc. To create this example, just create a blank query and paste in the code. Then you can test it with in the Query Editor interface. To use it, just create a new column that calls the function name with a parameter:

let
    fnHex2Dec = (input) =>
 
let
 
values = {
 
{"0", 0},
{"1", 1},
{"2", 2},
{"3", 3},
{"4", 4},
{"5", 5},
{"6", 6},
{"7", 7},
{"8", 8},
{"9", 9},
{"A", 10},
{"B", 11},
{"C", 12},
{"D", 13},
{"E", 14},
{"F", 15}
 
},
 
Result = Value.ReplaceType({List.First(List.Select(values, each _{0}=input)){1}},type {number})
 
in
 
Result
in
    fnHex2Dec

This function takes a single input parameter, a single text character and translates it to a decimal equivalent. You can test this function by clicking the "Invoke" button in the Power Query Editor window. Be sure to enter a single value preceded by a single quote, such as 'A. The single quote forces the input to be recognized as text. This ensures that if you enter a 7, that it is recognized as text instead of a number.

This is "M" code by the way.

To use it, create a new column in the Query Editor like:

=fnHex2Dec("A")

Anonymous - Here is an example of a lookup function that uses a database table as a source of its information:

let fnHex2DecFromDB = (input) =>

let
    Source = Sql.Database("MySQLServer", "MyDatabase"),
    dbo_Hex2Dec = Source{[Schema="dbo",Item="Hex2Dec"]}[Data],
    Record = Table.First(Table.SelectRows(dbo_Hex2Dec, each [Hex] = input)),
    Result = Record.Field(Record,"Dec")
in
    Result
in
    fnHex2DecFromDB

In the example above, "MySQLServer" is the name of the SQL Server, "MyDatabase" is the name of the database. In that database on that server I have a table called "Hex2Dec" with two columns named "Hex" and "Dec". The rows are what you would imagine them to be for hex to decimial conversion. Basically, the premise here should work for any lookup situation that has unique lookup values. To use this, just create a new column with the formula:

=fnHex2DecFromDB([TextColumn])