Create Conditional column based on date range from other table

[DavidNunes7]

Hello everybody. I have problem with power query
I would like to create a conditional column based on another table's date range

Below the example:

Thank you all for your attention

[DavidNunes7]

PERFECT
Tank you a lot!!!!

[AlienSx]

Hello, @DavidNunes7 

let
    tblA = your_table_A,
    tblB = your_table_B,
    tblB_mod = Table.RenameColumns(Table.DuplicateColumn(tblB, "Date", "Date End"), {"Date", "Date Start"}),
    a_b = tblB_mod & tblA,
    sorted = Table.Sort(a_b,{{"Date Start", Order.Ascending}, {"Date End", Order.Descending}, {"Text", Order.Descending}}),
    down = Table.FillDown(sorted,{"Text"}),
    flt = Table.SelectRows(down, each ([id] <> null)),
    rc = Table.RemoveColumns(flt,{"Date Start"})
in
    rc

[v-xinruzhu-msft · Accepted solution]

Hi @DavidNunes7 

You can create two blank query and put the following codes to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMta30DdU0oExjSyAbEelWB24pJElTNZS39gAyHZSio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Start" = _t, #"Date End" = _t, Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Start", type date}, {"Date End", type date}, {"Text", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1)
in
    #"Added Custom"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLWt9A3MleK1YlWMkISsVSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, Query1, {"Custom"}, "Query1", JoinKind.LeftOuter),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Merged Queries", "Query1", {"Date Start", "Date End", "Text"}, {"Date Start", "Date End", "Text"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Query1",{"Custom"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "Custom", each if [Date] >= [Date Start] and [Date] < [Date End] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Date Start", "Date End", "Custom"})
in
    #"Removed Columns1"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.