List.PositionOf Occurrence.First returns records but List.PositionOf Occurrence.All returns blanks?

[must_try_harder]

I have 2 tables:- 1 & 2 and I want to add a custom column to table 1 in power query which has a "Y" if the "item" in table 1 matches the "item" in table 2 and the "status_date" in table 1 is within "the location_start_date" and "location_end_date" range in table 2.

in power query I have both tables and have added custom columns to table1 to give me the first, last and all occurrences of the "location_start_date" but only the "Occurrence.First" and "Occurrence.Last work" expression work as expected - Occurence.All returns blanks? I can't see what I'm doing wrong - unless there a mistake in the expression maybe there is another way to do this?

First:
try table2[location_start_date]{List.PositionOf(
table2[item], [item],
Occurrence.First)} otherwise ""

Last:
try table2[location_start_date]{List.PositionOf(
table2[item], [item],
Occurrence.Last)} otherwise ""

All:
try table2[location_start_date]{List.PositionOf(
table2[item], [item],
Occurrence.All)} otherwise ""

 

[AlienSx]

"another way"

let
    tbl1 = Excel.CurrentWorkbook(){[Name="tbl_01"]}[Content],
    tbl2 = Excel.CurrentWorkbook(){[Name="tbl_02"]}[Content],
    tbl2_list = Table.ToList(
        tbl2, 
        (x) => 
            {
                {x{0}, x{1}, "Y"},
                {x{0}, x{2}, ""}
            }
    ), 
    tbl2_mod = Table.SelectRows(
        Table.FromList(List.Combine(tbl2_list), (x) => x & {true}, {"item", "status_date", "status", "tbl2"}), 
        (x) => x[status_date] <> null
    ), 
    combine_sort = Table.Sort(
        tbl1 & tbl2_mod, 
        {
            "item", 
            "status_date", 
            (x) => ((status) => if status = "Y" then 1 else if status = null then 2 else 3)(x[status])
        }
    ),
    fill_down = Table.FillDown(combine_sort, {"status"}), 
    result = Table.SelectRows(fill_down, (x) => x[tbl2] is null)
in
    result

 

[must_try_harder]

@AlienSx - it works, many thanks! (would still like to know whats going on with the Occurence.All thing for future reference but happy to go with anything that does the job).

 

[AlienSx]

@AlienSx - it works, many thanks! (would still like to know whats going on with the Occurence.All thing for future reference but happy to go with anything that does the job).

List.PositionOf with Occurrence.All returns a list of positions even if only single or no position is found. So that table_name[column_name]{list_of_positions} gives an error. Then "try...otherwise" statement goes "otherwise" way.

 

[smozgur]

Another approach:

let
    // Source tables and type transformations
    Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType1 = Table.TransformColumnTypes(Table1,{{"status_date", type date}}),
    Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    // Setting empty last date values with today's date which make sense to me. 
    // If the last status date in the future (12/01/2024) for pear in the same data is actually possible then this step can be adjusted
    ReplaceNullLastDates = Table.ReplaceValue(Table2,null, DateTime.LocalNow(), Replacer.ReplaceValue,{"location_end_date"}),
    ChangeType2 = Table.TransformColumnTypes(ReplaceNullLastDates,{{"location_start_date", type date}, {"location_end_date", type date}}),
    
    // Merge tables on "item" field to get grouped items in a table 
    Merge = Table.NestedJoin(ChangeType1, {"item"}, ChangeType2, {"item"}, "Found", JoinKind.LeftOuter),
    
    // Loop through rows and filter grouped tables for each item to match the required criteria
    Filter = Table.TransformRows(Merge,
        (r) => Record.TransformFields(r, 
            {"Found",  each if Table.IsEmpty(Table.SelectRows(_, each r[status_date] >= _[location_start_date] and r[status_date] <= _[location_end_date])) then "" else "Y"}
        )
    ),

    // The previous step generated a list or records, convert it back to a table
    ConvertToTable = Table.FromList(Filter, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RestoreTable = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"item", "status_date", "Found"})
in
    RestoreTable