Comaring data based on date

First I would like to remark that it is a bit strange to have "Deleted" on the last day of the resource. I would expect an additional row with the next date for that resource and status "Deleted".

Now you have an issue with 1 day resources: are they "New" or "Deleted"? In my solution below it will be "New".

In my solution, a resource will not be marked "New" on the first date that appears in the file (i.c. March 18); likewise a resource will not be marked as "Deleted" on the last date that appears in the file.

The logic for determining the status is: check for "New" then check for "Deleted" and what remains is "Existing".

Now the logic of the query:

In general if you need data from different rows, it's most efficient to get all the data you need on 1 row.

You can watch the first minute of this video (I created for another question) to see how you get data from the previous row on the current row. I applied the same technique for you (and similarly to get data from the next row on the current row).

1. The file is imported in Power Query (i.c. from the current Excel workbook) and automatically typed.
   I adjusted type datetime to date.
2. I first added an Index as Original sort so the results will be sorted back to the original sort at the end.
3. Sort on resource and date.
4. Now you need data from the previous and the next row.
   In order to get the data on the same row, I added 2 indices, starting with 0 and 1 respectively.
   Next the table is merged with itself twice: first joined on the 0-Index and 1-Index to get the data from the previous row,
   then on the 1-index and 0-index to get the data from the next row.
5. After each merge code was created, I adjusted the code and changed "NewColumn" in "Previous" resp. "Next"
6. After the merges you have 2 columns with nested tables that need to be expanded.
   Only the Resource is required. With expansion, keep the "Use original column name as prefix" checked,
   so you get Previous.Resource and Next.Resource.
7. I added 2 lines in the advanced editor to determine the min and max date in the file.
8. I added a column with the Status according to the logic I outlined above.
9. As finishing touches I sorted back to the original sort and removed all helper columns.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Resource", type text}, {"Amount", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Original Sort", 1, 1),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Resource", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index2",{"Index"},#"Added Index2",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"Index.1"},#"Merged Queries",{"Index"},"Next",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries1", "Previous", {"Resource"}, {"Previous.Resource"}),
    #"Expanded Next" = Table.ExpandTableColumn(#"Expanded Previous", "Next", {"Resource"}, {"Next.Resource"}),
    MinDate = List.Min(#"Sorted Rows"[Date]),
    MaxDate = List.Max(#"Sorted Rows"[Date]),
    #"Added Custom" = Table.AddColumn(#"Expanded Next", "Status", each if [Resource] <> [Previous.Resource] and [Date] <> MinDate then "New" else if [Resource] <> [Next.Resource] and [Date] <> MaxDate then "Deleted" else "Existing"),
    #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"Original Sort", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Original Sort", "Index", "Index.1", "Previous.Resource", "Next.Resource"})
in
    #"Removed Columns"