CODEWITHSUNDEEP
powerbipowerquery
mustafa00
mustafa00

Reputation: 881

How to find year in a string?

I try to write a custom function which is going to find a current or next year in a given string. Let's say I have a string in a Name column: A2022_new_accruals.xlsx. I need to return 1 if it's true, null if it's not. I wrote this function but it gives me error:

= if Text.Contains([Name], Date.ToText(Date.Year(DateTime.LocalNow()),"yyyy")) or Text.Contains([Name], Date.ToText(Date.AddYears(Date.Year(DateTime.LocalNow()),1),"yyyy")) then 1 else null

Error:

Expression.Error: Cannot convert value of Function type to Date type

Upvotes: 0

Views: 526

Answers (1)

horseyride
horseyride

Reputation: 21318

try

let Source = #table({"Name"},{{"A2021_new_accruals.xlsx"},{"A2022_new_accruals.xlsx"},{"A2023_new_accruals.xlsx"},{"A2024_new_accruals.xlsx"}}),
ThisYear=Date.Year(DateTime.LocalNow()),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Contains([Name],Text.From(ThisYear)) or Text.Contains([Name],Text.From(ThisYear+1)) then 1 else null)
in #"Added Custom"

Upvotes: 1

Related Questions