Power Query M Lookup on Same table with Filtering

Question

I already have an Excel Power Query that draws data from a "loading zone" table. Later, I want to add a macro that will cause the query to refresh, add the new data, and then delete the loading zone rows.

I required commenting to be available, and by self-referencing this query and extracting the manually made comments column, I was able to make commenting available. The query returns these and other rows.

With Power Query, I'm attempting to have it seek up comments for the same Device ID from the prior week.

As a result, "presenting concerns" should be Row 6's Last Week Comment value. There shouldn't be anything for the others since there were none last week.

The majority of available solutions, which I would prefer to avoid, are for Power BI and DAX setups.

Answer 1

In power query, merge the table on top of itself.

• match Device ID on top to Device ID on bottom
• match Last Week on top to Week on bottom
• hit [OK] then expand the Comment field using the arrows atop the new column

  

  let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  #"Merged Queries" = Table.NestedJoin(Source, {"Device ID", "Last Week"}, Source, {"Device ID", "Week"}, "Changed Type", JoinKind.LeftOuter),
  #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Comment"}, {"Last Weeks Comment"}),
  #"Changed Type" = Table.TransformColumnTypes(#"Expanded Changed Type",{{"Date", type date}, {"Week", type date}, {"Last Week", type date}}),
  #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}})
  in #"Sorted Rows"
  

  ID	Device ID	Date	Week	Last Week	Comment
  1	777	26/01/2023	22/01/2023	15/01/2023	presenting
  2	856	27/01/2023	22/01/2023	15/01/2023
  3	968	29/01/2023	22/01/2023	15/01/2023
  4	652	29/01/2023	22/01/2023	15/01/2023
  5	621	30/01/2023	29/01/2023	22/01/2023
  6	777	02/01/2023	29/01/2023	22/01/2023	fixed