Forum Discussion
Solved
Check if Date exists between 2 dates
I am using Power Query, and I have 2 tables that I am working with. I need to check if the route date (Table 1) exist in between 2 dates (from Table 2) and pull in the FY (Table 1). Table 1 Rout...
Not necessary to quote any other calendar table since the FY end date ("9/30") is fixed all the time. One step is enough,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9Q3MjC0VIrViVYy0zc0B/KMDMA8U30jEMdQKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Route Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Route Date", type date}}), FY = Table.AddColumn(#"Changed Type", "FY", each if Date.Month([Route Date]) > 9 then "FY" & Date.ToText(Date.EndOfYear([Route Date])+#duration(1,0,0,0), "yy") else "FY" & Date.ToText([Route Date], "yy")) in FY
Not necessary to quote any other calendar table since the FY end date ("9/30") is fixed all the time. One step is enough,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9Q3MjC0VIrViVYy0zc0B/KMDMA8U30jEMdQKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Route Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Route Date", type date}}),
FY = Table.AddColumn(#"Changed Type", "FY", each if Date.Month([Route Date]) > 9 then "FY" & Date.ToText(Date.EndOfYear([Route Date])+#duration(1,0,0,0), "yy") else "FY" & Date.ToText([Route Date], "yy"))
in
FY
Thank you!!