Very quick 'IF function' question

[LT4RFF]

Hello,

I have a column with multiple accounts.

On each of those accounts I am responsible for a percentage.

I would like to create a separate column and have a formula 'IF account = B1234 then % = 25' 'IF account = B5678 then % = 50%'

So a new column with the percentages running down corresponding to each account.

What formula do I enter to do that please?

[ronrsnfld · Accepted solution]

If you only have a few accounts to add the percentage, the the formula provided by @PwerQueryKees should be sufficient.

If you have a large number of accounts to consider, you would be better off creating a table of accounts vs reponsible percentages which you could then refer to for your Custom column.

Paste the below code into the Advanced Editor to see how that would work. In your real data, you would create the Responsible and Data tables separately.

let

//sample table of responsibility
    #"Responsible Percent" = Table.Buffer(Table.FromRecords({
        [Account="B1234", Percent=0.25],
        [Account="B5678", Percent=0.50]},
        type table[Account=text, Percent=Percentage.Type])),

//sample data table
    Source = Table.FromRecords({
        [Account="B1234"],
        [Account="B5678"],
        [Account="A9876"],
        [Account="A1579"],
        [Account="C1234"]
        },type table[Account=text]),
    
//Add responsible percent column per account
    #"Added Custom" = Table.AddColumn(Source, "Responsible Percentage", 
        each 
            try Table.SelectRows(#"Responsible Percent",(r)=>
                r[Account]=[Account])[Percent]{0} 
            otherwise 0, Percentage.Type)
in
    #"Added Custom"

Responsible Table

Results

[LT4RFF]

Hello,

Apologies for the delay in resonding.

I think your answer is the best way forward for me as it means I can add more accounts to that list every year.

With that said I have got myself in a tangle trying to follow your answer.

I have now created a separate table in a separate excel spreadhseet with the account name and it's corresponding percentage.

Account	Percent
B0429BALT	25.00%
B0429BAAB	25.00%
B0507NALB4730024A
B0429BARBT	50.00%
B0429BA2100128
B0429BASBT	50.00%
B0429BAPE	100.00%
B2429BWMB	100.00%

That is now saved as a separate spreadhseet.

and now I'm stuck.

What code am I entering?  How do I get this spreadsheet into my power query?

[ronrsnfld]

In Excel, you would

1. Select a cell in the table
2. Navigate Data=>Get & Transform=>From Table/Range

And then in your main query, you would refer to it by the name you see in either side panel that refers to that query (replacing #"Responsible Percent"). Or you could just rename that query #"Responsible Percent".

[Omid_Motamedise]

What is B1234 , if they are text you should put them inside ""

[LT4RFF]

No I want to do this in Power Query.

Get the data in and transform it.

[Oluwapelumi]

Then @PwerQueryKees has helped with the solution but kindly note that Power Query is CASE sensitive. You must be it the same way you have it in your data or else you may not get your expected output.

[PwerQueryKees]

No swith in Power Query.

so you formula would use in the add column dialog would be:

= if account = "B1234" then 0.25 else if account = "B5678" then 0.50 else 0

[Oluwapelumi]

Ohh...I thought the formula was to be used in Excel.

[Oluwapelumi]

You can use SWITCH function in Excel.

=SWITCH(D4, "B5678", "25%", "B1234", "50%") or =SWITCH(D4:D25, "B5678", "25%", "B1234", "50%")

You can use either of the formula above. For the first one, you select the first row under the column while for the second, you highlight all the rows under the column at once but this will make it an array.

Kindly let me know if this help.