cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Achartuni
Helper I
Helper I

Change numeric values in a column based on the last 2 digits

Hello Power BI community,

 

I have the following data sample, 201010, 201030, 201110, 201130. Basically I have the year and the last two digits are either 10 or 30.

 

What I want to do is change the values in a way that if the value ends in 10 it adds 20 to the value. An example of this would be 201910 turns into 201930. But if the value ends in 30 I want to add 80 to the value so if the value is 201930 it turns into 202010. 

 

Is there any easy way to do this? Thanks in advance

1 ACCEPTED SOLUTION
BA_Pete
Super User II
Super User II

Hi @Achartuni ,

 

You can do this in Power Query.

Create a new custom column and write the following, where "original" is th name of your column that holds the six-digit references:

 

if Text.End([original], 2) = "10" then Number.From([original]) + 20 else Number.From([original]) + 80

 

 

This gives me the following output:

Achartuni.PNG

 

You can also just overwrite the exisiting column values, rather than creating a new column by adding a custom step in Power Query, then applying the following code:

= Table.ReplaceValue(#"Added Custom",each [original], each if Text.End([original], 2) = "10" then Number.From([original]) + 20 else Number.From([original]) + 80,Replacer.ReplaceValue,{"original"})

 

again, replacing any "original" with your column name.

 

This just overwrites the original column as follows:

Achartuni2.PNG

 

Pete

View solution in original post

2 REPLIES 2
ryan_mayu
Super User II
Super User II

@Achartuni 

You can also try this

Column = 
VAR NUM=RIGHT('Table (2)'[Column1],2)
RETURN if(NUM="10",'Table (2)'[Column1]+20,if(NUM="30",'Table (2)'[Column1]+80))

 1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




BA_Pete
Super User II
Super User II

Hi @Achartuni ,

 

You can do this in Power Query.

Create a new custom column and write the following, where "original" is th name of your column that holds the six-digit references:

 

if Text.End([original], 2) = "10" then Number.From([original]) + 20 else Number.From([original]) + 80

 

 

This gives me the following output:

Achartuni.PNG

 

You can also just overwrite the exisiting column values, rather than creating a new column by adding a custom step in Power Query, then applying the following code:

= Table.ReplaceValue(#"Added Custom",each [original], each if Text.End([original], 2) = "10" then Number.From([original]) + 20 else Number.From([original]) + 80,Replacer.ReplaceValue,{"original"})

 

again, replacing any "original" with your column name.

 

This just overwrites the original column as follows:

Achartuni2.PNG

 

Pete

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.