Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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
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
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?
In Excel, you would
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".
What is B1234 , if they are text you should put them inside ""
No I want to do this in Power Query.
Get the data in and transform it.
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.
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
Ohh...I thought the formula was to be used in Excel.
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.