Forum Discussion

Qlik333's avatar
Qlik333
Regular Visitor
4 years ago
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...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago

    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

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

Here is an alternative way by using Table.SelectRows - Solution file uploadd to -  

https://1drv.ms/x/s!Akd5y6ruJhvhuR_VfVLkonWse4WC?e=VrKpVz 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Route Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(Table2, (x)=> x[End]>=[Route Date] and x[Start]<=[Route Date]){0}[FY])
in
    #"Added Custom"