Power Query custom column to get data from a different row

Question

This might be simple, but I can't wrap my head around it... I need a Custom Column in Power Query to return data from a specific column in another row

Currently I have location data for all employee ID numbers, but for some, the location is blank. In this data, in any given employee's row, there is also their manager's ID#.

What I need is a custom row that returns the employee's manager's location IF the employee's location is blank. For now, I am not looking to fix manager's that also do not have a location, if the manager's location is blank, I am ok with the Employee's pulling blanks in these cases only.

Any help would be greatly appreciated.

Answer 1

Merge the table on top of itself using the manager ID column on top matched to the employee ID column on bottom.

Expand [x] location using the arrows atop the new column.

Add column ... custom column ... with formula

=if [location] =null then [putnameofnewcolumnyouexpandedhere] else [location]

or

= [location] ?? [putnameofnewcolumnyouexpandedhere]

Right click remove extra columns

let  Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source, {"ManagerID"}, Source, {"ID"}, "Source", JoinKind.LeftOuter),
#"Expanded Source" = Table.ExpandTableColumn(#"Merged Queries", "Source", {"Location"}, {"Manager.Location"}),
#"Added Custom" = Table.AddColumn(#"Expanded Source", "CombinedLocation", each if [Location]=null then [Manager.Location] else [Location]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Location", "Manager.Location"})
in  #"Removed Columns"

Answer 2

You can do something like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddLocation 
        = Table.AddColumn(
            Source,
            "LocationCleaned",
            (a) => 
                if  a[location] = null then 
                    Table.SelectRows(Source, each [emp ID] = a[manager] )[location]{0}
                else 
                    a[location]
        )
in
    AddLocation