Use Index Column to Create Date (of Years) Column base on Another Table Start Date

[cflynn_29]

Hello,

I have an Index Column that represents the number of years that the data is calculated to (Year Index) and i have another table that has the start date (year) of when this data claculation starts, in this case its 2024.

I would like to insert a new column that starts at Index 1 & 2024 and then follows that index until 2049. 

Thank you,

[v-yanjiang-msft · Accepted solution]

Hi @cflynn_29 ,

According to your description, here's my solution. Add a custom column.

Date.Year(#"Date"[Date]{0})+[Year Index]-1

Get the correct result:

Here's the whole M syntax:

Date table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE11DVUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
in
    #"Changed Type"

Index table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY25EYBAEMN62ZgAm++oZef6b4Mbi0SOJHeXam5dDo/wDK/wDp/wDbUzeEIUplCFK2RhazBUTMX/OxVTMRWvyvwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Year Index" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year Index", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year(#"Date"[Date]{0})+[Year Index]-1)
in
    #"Added Custom"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daryl-Lynch-Bzy]

Hi @cflynn_29  - you have a couple of options to consider.
The Table.AddIndexColumn - PowerQuery M | Microsoft Learn can start at 2022, 2023, 2024 and increment up.

You can add 2023 to each row to get result.

You convert to date you need to use the following function:  #date( [Year] , 1, 1 )