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 DataAccountNoAccTypeCustomerPoints1111X1X111112561111X1.1X1.111115891111X1.2X1.21111100021111X1.3X1.311115551111X1.4X1.411116981111X1.5X1.511111481111X1.6X1.6111126991111X1.7X1.71111369851111X1.8X1.8111115541111X1.9X1.9111121111X1.10X1.101111332222X1X12222982222X1.1X1.122229339999X20X209999784569999X20.1X20.199993657777M2M2777723587777M2.1M2.1777723 This is what I am looking to have it look likeAccountNoAccTypeAccountNameCustomerPoints1111X1X1Silver Account11112561111X1.1X1.1Silver Account11115891111X1.2X1.2Silver Account1111100021111X1.3X1.3Silver Account11115551111X1.4X1.4Silver Account11116981111X1.5X1.5Silver Account11111481111X1.6X1.6Silver Account111126991111X1.7X1.7Silver Account1111369851111X1.8X1.8Silver Account111115541111X1.9X1.9Silver Account111121111X1.10X1.10Silver Account1111332222X1X1Silver Account2222982222X1.1X1.1Silver Account22229339999X20X20Bronze Account9999784569999X20.1X20.1Bronze Account99993657777M2M2Misc Account777723587777M2.1M2.1Misc Account777723

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-trendPower-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-RangesConnect on Linkedin

Anonymous

Not applicable

6 years ago

Solved

Replace or Lookup Values

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

Need Help

Tips and Tricks

amitchandak
6 years ago
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

3 Replies

amitchandak
Super User
6 years ago
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
  Not applicable
  6 years ago
  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
    Super User
    6 years ago
    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

Forum Discussion

Replace or Lookup Values

3 Replies

Related content

Replacing values with Iterations

Replace Errors values with Null values

Replace values

Lookup rank value?

how to dynamically replace values for multiple columns

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