Forum Discussion
Add years from variable to a date
I am helping provide a security form wherein security is provided for N years (dependant on the security type). My data entries are for "last completed" security approval and I need to add Completion+N (we set 0 for non-renewing security) to the form.
Basics I have checked:
* Number or Date format setting(s) on column(s) - ALL columns involved
* Hidden spaces in my matching IDs/connecting columns
I have a SECURITYTABLE
| Security ID | Years |
| Text | attempted whole number and decimal number - same thing |
| WKK123 | 0 |
| WKK321 | 1 |
| WKK333 | 3 |
I have this table linked to my other table by "Security ID"
I then have my PRIMARYTABLE listing users, Security, and Date completed (and a bunch of other data, but I'm trying to keep it simple).
| User Id | Security ID | Completion Date | Expiration Date (Trying to ADD) |
| Text | Text | Date (mm/dd/yyyy) | Date (mm/dd/yyyy) |
| ABCD | WKK333 | 05/04/2023 | |
| BBBA | WKK123 | 10/15/2022 | |
| BBBA | WKK321 | 10/15/2022 |
First I tried a simple "Date" column addition
Expiration Date=DATE(YEAR(PRIMARYTABLE[Completion Date])+LOOKUPVALUE(SECURITYTABLE[Years],SECURITYTABLE[Security ID],PRIMARYTABLE[Security ID])),MONTH(PRIMARYTABLE[Completion Date]),DAY(PRIMARYTABLE[Completion Date]))
I Googled and found DATEADD so I tried that instead
Expiration Date=DATEADD(PRIMARYTABLE[Completion Date],LOOKUPVALUE(SECURITYTABLE[Years],SECURITYTABLE[Security ID ],PRIMARYTABLE[Secuirty ID]),YEAR)
RANDOM rows have data. And I mean Random. All 0's and SOME 1's. I even added the "Lookup" column to see if there was ANY pattern. I feel like I am losing my mind.
Hi LMSReportsHelp ,
Looking at the example is difficult to pin point what can be the error, if the table are related instead of using the LOOKUPVALUE use the RELATED function this would be something similar to:
Expiration Date = DATEADD ( PRIMARYTABLE[Completion Date], RELATED ( SECURITYTABLE[Years] ), YEAR )However this type of transformation is better achieved in the Power query in this case you can do a merge between both tables based on the Security ID and then add a column with the following syntax:
Date.AddYears ([Date], [Years])Hi LMSReportsHelp ,
The Power Query can do it using a merge table please follow the steps below:
- Do a merge from the Primary to the security table
- Expand the Year column
- Add the new column
See full code for this below:
// Primary let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydlHSUQr39jY2NgYyDEz0DUz1jQyMjJVidaKVnJycHCHShkYgaUNTfUMDkLQRmrSxkSGadCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, #"Security ID" = _t, #"Completion Date" = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"User ID", type text}, {"Security ID", type text}, {"Completion Date", type date}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Security ID"}, SECURITY, {"Security ID"}, "SECURITY", JoinKind.LeftOuter), #"Expanded SECURITY" = Table.ExpandTableColumn(#"Merged Queries", "SECURITY", {"Years"}, {"Years"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded SECURITY",{{"Years", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Expiration Date", each Date.AddYears ([Completion Date], [Years])) in #"Added Custom" // SECURITY let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvf2NjQyVtJRMlCK1QFzjY0MgVxDONcYJGusFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Security ID" = _t, Years = _t]) in Source
3 Replies
Hi LMSReportsHelp ,
Looking at the example is difficult to pin point what can be the error, if the table are related instead of using the LOOKUPVALUE use the RELATED function this would be something similar to:
Expiration Date = DATEADD ( PRIMARYTABLE[Completion Date], RELATED ( SECURITYTABLE[Years] ), YEAR )However this type of transformation is better achieved in the Power query in this case you can do a merge between both tables based on the Security ID and then add a column with the following syntax:
Date.AddYears ([Date], [Years])Ok, but can Power Query reference the 2nd table's column? I need it to pull the variable, I am a novice with Power Queries and I didn't think they could do that. Am I wrong?
Hi LMSReportsHelp ,
The Power Query can do it using a merge table please follow the steps below:
- Do a merge from the Primary to the security table
- Expand the Year column
- Add the new column
See full code for this below:
// Primary let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydlHSUQr39jY2NgYyDEz0DUz1jQyMjJVidaKVnJycHCHShkYgaUNTfUMDkLQRmrSxkSGadCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"User ID" = _t, #"Security ID" = _t, #"Completion Date" = _t]), #"Changed Type1" = Table.TransformColumnTypes(Source,{{"User ID", type text}, {"Security ID", type text}, {"Completion Date", type date}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Security ID"}, SECURITY, {"Security ID"}, "SECURITY", JoinKind.LeftOuter), #"Expanded SECURITY" = Table.ExpandTableColumn(#"Merged Queries", "SECURITY", {"Years"}, {"Years"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded SECURITY",{{"Years", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Expiration Date", each Date.AddYears ([Completion Date], [Years])) in #"Added Custom" // SECURITY let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvf2NjQyVtJRMlCK1QFzjY0MgVxDONcYJGusFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Security ID" = _t, Years = _t]) in Source
