Search code examples
excel-formulapowerqueryxlookup

XLOOKUP equivalent in Power Query

I have a simple XLOOKUP formula that looks up a string in a master_list sheet and if that exists in the weekly_update sheet, it should update the contents of selected columns in the master_list with the contents in the weekly_update sheet.

Currently, I do this manually with XLOOKUP but since it is many columns and needs to be done every few days, I am trying to implement it using PQ. The current code I have for the merge query (Source1 = master_list, Source2 = weekly_update):

let
    // Merge Source1 and Source2
    MergedTables = Table.NestedJoin(Source1, {"ID Number"}, Source2, {"ID Number"}, "Source2Data", JoinKind.LeftOuter),

    // Expand Source2 columns
    ExpandedSource2Data = Table.ExpandTableColumn(MergedTables, "Source2Data", 
        {"Title", "Description", "Status", "Disposition"}, 
        {"Source2_Title", "Source2_Description", "Source2_Status", "Source2_Disposition"}), 

    // Replace columns from Source1 with columns from Source2 (Conditional logic with change check and null handling)
    UpdatedColumns = Table.TransformColumns(ExpandedSource2Data, {
        {"Title", each if [Source2Data] <> null and (try Text.From([Title]) <> [Source2Data][Title] otherwise false) then [Source2Data][Title] else [Title], type text},
        {"Description", each if [Source2Data] <> null and [Description] <> [Source2Data][Description] then [Source2Data][Description] else [Description], type text},
        {"Status", each if [Source2Data] <> null and [Status] <> [Source2Data][Status] then [Source2Data][Status] else [Status], type text},
        {"Disposition", each if [Source2Data] <> null and [Disposition] <> [Source2Data][Disposition] then [Source2Data][Disposition] else [Disposition], type text}
    }),

    // Remove temporary columns 
    FinalTable = Table.RemoveColumns(UpdatedColumns,
        {"Source2_Title", "Source2_Description", "Source2_Status", "Source2_Disposition"})

in
    if Table.RowCount(FinalTable) > 0 then FinalTable else null  // Return the entire table or null if empty

This is always returning the merged table without updating the columns and I can't figure out what I am missing.

PS: I am brand new to Power Query so, apologize for any obvious oversights.

Solution

  • One way to do this in Powerquery:

    //code for Master Table
let Source = Excel.CurrentWorkbook(){[Name="SourceDataMasterList"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source, {"ID Number"}, weekly_update, {"ID Number"}, "weekly_update", JoinKind.LeftOuter),
Replace1=Table.ReplaceValue(#"Merged Queries",  each  [Title], each  [weekly_update][Title]{0}??[Title]  ,Replacer.ReplaceValue,{"Title"}),
Replace2=Table.ReplaceValue(Replace1,  each  [Description], each  [weekly_update][Description]{0}??[Description]  ,Replacer.ReplaceValue,{"Description"}),
Replace3=Table.ReplaceValue(Replace2,  each  [Status], each  [weekly_update][Status]{0}??[Status]  ,Replacer.ReplaceValue,{"Status"}),
Replace4=Table.ReplaceValue(Replace3,  each  [Disposition], each  [weekly_update][Disposition]{0}??[Disposition]  ,Replacer.ReplaceValue,{"Disposition"}),
#"Removed Columns" = Table.RemoveColumns(Replace4,{"weekly_update"})
in #"Removed Columns"

    enter image description here

    Note x??y will return x unless x is null, in which case y is returned