Forum Discussion
Helper IUse Index Column to Create Date (of Years) Column base on Another Table Start Date
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,
Hi cflynn_29 ,
According to your description, here's my solution. Add a custom column.
Date.Year(#"Date"[Date]{0})+[Year Index]-1Get 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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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 )
v-yanjiang-msftCommunity Support
Hi cflynn_29 ,
According to your description, here's my solution. Add a custom column.
Date.Year(#"Date"[Date]{0})+[Year Index]-1Get 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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
