Power Query M Lookup on Same table with Filtering-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

Here's another method using the Table.Group method:

Please read the code and the code comments to better understand the algorithm

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"ID", Int64.Type}, {"Device ID", Int64.Type}, 
        {"Date", type date}, {"Week", type date}, {"Last Week", type date}, 
        {"Comment", type text}, {"Last Week Comments", type text}}),

//Group by Device ID
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Device ID"}, {
        {"lwc", (t)=> 
            let 

        //Extract Last Weeks comment
        //Then insert into the Last Week Comment Column corresponding to This Week
                #"This Week" = List.Max(t[Week]),
                #"Last Week" = Date.AddDays(#"This Week",-7),
                #"Last Week Comment" = try Table.SelectRows(t, each [Week] =  #"Last Week")[Comment]{0} otherwise null,
                #"Add to Column" = Table.FromRecords(Table.TransformRows(t,
                    (r) => Record.TransformFields(r,
                        {"Last Week Comments", each if r[Week]=#"This Week" then #"Last Week Comment" else _})))
                 
            in 
                #"Add to Column"
                
                }
            }),

//Reexpand the grouped table
    #"Expanded lwc" = Table.ExpandTableColumn(#"Grouped Rows", "lwc", 
        {"ID", "Date", "Week", "Last Week", "Comment", "Last Week Comments"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded lwc",
        {"ID",  "Device ID", "Date", "Week", "Last Week", "Comment", "Last Week Comments"}),

//Sort back to original order using the original ID column
//If that is not in the desired sort order, add an Index column at the beginning,
//   and use that to sort the data
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"ID", Order.Ascending}}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{
        {"ID", Int64.Type}, {"Device ID", Int64.Type}, 
        {"Date", type date}, {"Week", type date}, {"Last Week", type date}, 
        {"Comment", type text}, {"Last Week Comments", type text}})
in
    #"Changed Type1"