Using the "IF" function to create a new column

[Anonymous]

Hello,

I need to create a new column and it must use the condition "IF", for example:

= If (Provider = '05521463000112 - CAMARA MUNICIPAL TERESINA', 'Transfer to Camara' 

if (Provider = '41256744000159 -. IPMT- INST.PREV.DOS SERV MUN', 'Transfer to IPMT'))

I got to like açgo using the "SUM" function but for this case I can not use the SUM, how can I do this?

[Sean]

@Anonymous

Column = IF ( TableName[Provider Column]="'05521463000112 - CAMARA MUNICIPAL TERESINA", "Transfer to Camara",

                  IF ( TableName[Provider Column]="41256744000159 -. IPMT- INST.PREV.DOS SERV MUN" , "Transfer to IPMT ) )

Why do you need SUM?

Do need to CONCATENATE something?

[Greg_Deckler]

Depends on whether this is DAX or M

If DAX, the syntax is:

NewColumn = IF(Table[Column] = "Some value","true condition","false condition")

In M this would be:

=if [Column]="Some value" then "true condition" else "false condition"

[Anonymous]

@Greg_Deckler

Thus could apply only if I use a value of a given field, as I do in the event of two conditions as the example that I spent?

[Greg_Deckler]

@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])

[Greg_Deckler]

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")

[achinm45]

Hi @Greg_Deckler,

Do we have invoke function in Power BI ?

Thanks and Regards

Achin

[Greg_Deckler]

I'm not sure what you mean by an "invoke" function?

[Anonymous]

@Greg_Deckler

Sorry but I could not understand

[Greg_Deckler]

@Anonymous - Perhaps I'm not understanding your request, but basically, if you have a SQL table (or really ANY source) of "Lookup values" and "Replacement values" like:

Lookup,Replace

05521463000112 - CAMARA MUNICIPAL TERESINA,Transfer to Camara

41256744000159 -. IPMT- INST.PREV.DOS SERV MUN, Transfer to IPMT

Create a new blank query and paste in the following code provided and change MYSqlServer, MyDatabase, the table name and the column names to fit your data model. If you give me your source, column names and other information I can write you the exact function.

[Anonymous]

thank you but I ended up solving this in my ETL process