Power Query M Lookup on Same table with Filtering

Question

I already have a Power Query on Excel that feeds off an Excel "loading zone" table. I plan to add a macro later to have the query refresh to add the new rows and delete loading zone rows afterwards.

I needed commenting to be available, which I was able to achieve by self-referencing this query and extracting the manually created comments column. The query has these and more rows.

What I'm now trying to achieve with Power Query is for it to lookup comments for the same Device ID but for the previous week.

Row 6 should therefore have "presenting issues" as a Last Week Comment value. The others should not have anything as there are no comments from the previous week.

Most solutions I see out there are for Power BI and DAX environments, which I would like to avoid.

Thanks community in advance.

Answer 1

In powerquery, 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