Creating a new range column in Power Query

[Anonymous]

Hi,

I'm currently working with some survey data in Power BI and struggling to creating a new range column based on two other columns. The collection of age data has changed at some point through the years from exact age to an age range and is not aligned (some rows has exact age, some has age range, some has both and some neither). I'm therefore interested in aligning and unifying the age data in a new column similar to the age range column. I've created min value and max value columns to hopefully do this in a sorta 'lookup'-type fashion. 

So, its the yellow column [New Age] I would like to create and to be dynamic if other ranges were to be added later - with logic somewhat like this:

if [nyaldgrp] > 0 then [nyaldgrp]

else if [alder] >= [Min_Value] and <= [Max_Value] then [Age_BK]

I hope you'll able to help

[Vera_33 · Accepted solution]

Hi @Anonymous 

Your Age Dimension table has all the unique entries, right? Here is one way, added one row to show the result

(x)=> if x[nyaldrp]>0 then x[alder]
else dimAge[Age_BK]{ List.PositionOf(dimAge[Custom],List.Select(dimAge[Custom], each List.Contains(_,x[alder])){0})}?

Added one Custom column in dimAge