Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello - I have a column with the age of the company , ranging from age 1 to 100 , I'm trying to create a new custom column (Company Age Range) in power query that will give the value something like below this from the age column - how can i do this ? thank you!
Company Age Range |
0 - 2 |
3 - 5 |
6 - 10 |
11 - 20 |
21 - 30 |
31 - 40 |
41 - 50 |
51 - 100 |
Over 100 |
N/A |
Solved! Go to Solution.
The easiest way is to use a custom column. The Conditional Column feature won't work as it will try to interpret 6-10 as a date.
The formula would look like this:
if [Age] <= 2 then "0-2"
else if [Age] <= 5 then "3-5"
else if [Age] <= 10 then "6-10"
else null
Just continue adding more 'else if' clauses until you have it like you want. You must include the final "else" statement, so it could be else null, or your final result, else "N/A"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe easiest way is to use a custom column. The Conditional Column feature won't work as it will try to interpret 6-10 as a date.
The formula would look like this:
if [Age] <= 2 then "0-2"
else if [Age] <= 5 then "3-5"
else if [Age] <= 10 then "6-10"
else null
Just continue adding more 'else if' clauses until you have it like you want. You must include the final "else" statement, so it could be else null, or your final result, else "N/A"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingthank you so much for this solution- It really worked 🙂
Excellent @sks2701 - glad to be of service!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting