Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
ryan_mayu
Super User
Super User

@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
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.