Determining Generation

[zfrank67]

Hey all!

I've got a table that has employee information, including Date of Birth. Then I also have a reference table that lists each generation with a start year and end year.

I'd like to take the value of Date.Year([Date Of Birth) in Table1 and pull back which generation it is in. That is, see if the year value of the first table is between the upper and lower limits columns in the second table and return associated information from another column in the second table.

[zfrank67]

@edhans Took me a few days to be able to test, but this worked perfectly. Thank you!

[edhans]

Great @zfrank67 - glad I was able to assist.

[edhans · Accepted solution]

Hi @zfrank67 ,

You will either want to do this in DAX where it is very fast to filter tables between ranges, or you need to expand this table for the "lookup" so all values are listed. I started with this mockup:

I then added a new Year Range column with the following formula:

={[Start Year]..[End Year]}

That adds a new column where every year from start to finish is listed. Expand that to new rows:

You will get this, one record for every year:

Now just use the Merge feature of Power Query. Merge the birth year to this column, then expand the Generation column you want to display.

This table would only be used as a lookup. I would not bring this table into a data model without knowing more about what you are doing.

Full code here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck/NM1TSUTK0tDQHUkYGRkZKsTpgYSOwsAVU1gwmbAzmm5lCZA1gwiZgvglE2MxEKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Generation = _t, #"Start Year" = _t, #"End Year" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Year", Int64.Type}, {"End Year", Int64.Type}}),
    #"Added Year Range" = Table.AddColumn(#"Changed Type", "Year Range", each {[Start Year]..[End Year]}),
    #"Expanded Year Range" = Table.ExpandListColumn(#"Added Year Range", "Year Range")
in
    #"Expanded Year Range"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.