Creating a new range column in Power Query

Question

Hi,&nbsp;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.&nbsp;&nbsp;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] &gt; 0 then [nyaldgrp]else if [alder] &gt;= [Min_Value] and &lt;= [Max_Value] then [Age_BK]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I hope you'll able to help

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;&nbsp;Your Age Dimension table has all the unique entries, right? Here is one way, added one row to show the result&nbsp;&nbsp;&nbsp;(x)=&gt; if x[nyaldrp]&gt;0 then x[alder]
else dimAge[Age_BK]{ List.PositionOf(dimAge[Custom],List.Select(dimAge[Custom], each List.Contains(_,x[alder])){0})}?&nbsp;Added one Custom column in dimAge&nbsp;&nbsp;&nbsp;

Forum Discussion

Creating a new range column in Power Query

1 Reply

Related content

Create columns group

Create custom column with a range of number

Creating a new measure from date ranges

Creating a date column

Creating a custom column