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

Route Date      FY

10/1/2019

6/17/2020

5/2/2021

 

Table 2

Start               End                   FY

10/1/2019      9/30/2020         FY20

10/1/2020      9/30/2021         FY21

10/1/2021      9/30/2022         FY22

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

     

4 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    Here's one way:

     

    let

//Read in the dates table
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    dateTable = Table.TransformColumnTypes(Source,{{"Route Date", type date}}),

//Read in the FY table
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    fyTable=Table.TransformColumnTypes(Source2,{
        {"Start", type date},{"End", type date},{"FY", type text}
        }),

//Add a column to the FY table that includes all of the dates
    #"Added Custom" = Table.AddColumn(fyTable, "Custom", each List.Dates([Start], Duration.Days([End]-[Start])+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),

//Join the two tables (JoinKind.LeftOuter) and return the FY
    joined = Table.Join(dateTable,"Route Date",#"Expanded Custom","Custom",JoinKind.LeftOuter),

//Remove unneeded columns
    #"Removed Columns" = Table.RemoveColumns(joined,{"Start", "End", "Custom"})
in
    #"Removed Columns"

     

     

     

     

     

  • 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"

     

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    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