Replace or Lookup Values

[Anonymous]

Hi,

I am relatively new to Power BI. While I am quite comfortable with Excel and SQL, I am finding Power BI a bit of a challenge for some of the things I am wanting to do.

This one seems like quite a simple one, but clearly, I am missing something in terms of what do.

I have a table that I am looking to transform/replace some of the values to clean up the data.

The original data and what I am looking to produce is below.

AccType's with an X1 is an Account Name of ‘Silver Account’, but in the instance of the same Customer (1111 in the example) can have many ‘Silver Account’. So I want to say anything with X1 or anything X1.* (wildcard) is also a ‘Silver Account’.

In PBI, I can certainly create a replace X1 with ‘Silver Account’ and X1.1 with ‘Silver Account’ and so on, but this would take a very long time as there could go up to X1.999 and then there are several other types as well… e.g. X20 or X20.* is ‘Bronze Account’ and M2 or M2.* is ‘Misc Account’.

Is there a way to have a replace that use can use a wildcard or perhaps even a lookup table that has the definitions of the accounts stored in another table and then referred to in PBI?

Here is the example data....

Here is the Source Data

AccountNo	AccType	Customer	Points
1111X1	X1	1111	256
1111X1.1	X1.1	1111	589
1111X1.2	X1.2	1111	10002
1111X1.3	X1.3	1111	555
1111X1.4	X1.4	1111	698
1111X1.5	X1.5	1111	148
1111X1.6	X1.6	1111	2699
1111X1.7	X1.7	1111	36985
1111X1.8	X1.8	1111	1554
1111X1.9	X1.9	1111	2
1111X1.10	X1.10	1111	33
2222X1	X1	2222	98
2222X1.1	X1.1	2222	933
9999X20	X20	9999	78456
9999X20.1	X20.1	9999	365
7777M2	M2	7777	2358
7777M2.1	M2.1	7777	23

This is what I am looking to have it look like

AccountNo	AccType	AccountName	Customer	Points
1111X1	X1	Silver Account	1111	256
1111X1.1	X1.1	Silver Account	1111	589
1111X1.2	X1.2	Silver Account	1111	10002
1111X1.3	X1.3	Silver Account	1111	555
1111X1.4	X1.4	Silver Account	1111	698
1111X1.5	X1.5	Silver Account	1111	148
1111X1.6	X1.6	Silver Account	1111	2699
1111X1.7	X1.7	Silver Account	1111	36985
1111X1.8	X1.8	Silver Account	1111	1554
1111X1.9	X1.9	Silver Account	1111	2
1111X1.10	X1.10	Silver Account	1111	33
2222X1	X1	Silver Account	2222	98
2222X1.1	X1.1	Silver Account	2222	933
9999X20	X20	Bronze Account	9999	78456
9999X20.1	X20.1	Bronze Account	9999	365
7777M2	M2	Misc Account	7777	2358
7777M2.1	M2.1	Misc Account	7777	23

[amitchandak]

Try

Account Name= SWITCH(TRUE(),SEARCH("X1",Sheet1[AccType],1,0)>0,"Silver Account",SEARCH("X2",Sheet1[AccType],1,0)>0,"Bronze Account","Misc Account")

pbix:https://www.dropbox.com/s/mpdu188qw33eibv/accountType.pbix?dl=0

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

Thanks heaps... I have just tried this logic, but I have a scenario where it does not always work.

e.g. I have another account type of X12 (and subsequently X12.*) which is another account type.

I almost need a formula than will provide something like...

Any value is exactly "X1" then 'Sivler Account' and "X1.*" then 'Silver Account'
Then would be able to do other Account Types by following the same style of formula.

Sorry I should have mentioned that in my original message... I have others such as X4 type accounts that are different to an X40.

I have a reference table which I thought might have been an option as a simple lookup and I could just update the reference table instead of the formula if there is ever an extra account type added.

The Reference table looks something like this...

Acct Type	Account Name
X1	Silver Account
X20	Bronze Account
X12	Blue Account
M2	Misc Account
S4	Red Account
S40	Yellow Account

[amitchandak · Accepted solution]

Try like this. In Switch move all Exact values first. If true it will not check others

Flag = SWITCH(TRUE(),Sheet1[AccType]="X1", "Silver Account"
			,Sheet1[AccType]="S4", "Red Account"
			,SEARCH("X1",Sheet1[AccType],1,0)>0,"Blue Account"
			,SEARCH("X2",Sheet1[AccType],1,0)>0,"Bronze Account"
			,SEARCH("S40",Sheet1[AccType],1,0)>0,"Yellow Account"
				,"Misc Account")

This means if the first value is true it will not check second. So just order in that manner.

Take the updated file from dropbox link again

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin