Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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:
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:
Pete
Proud to be a Datanaut!
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))
Proud to be a 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:
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:
Pete
Proud to be a Datanaut!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |