Power Query custom column to get data from a different row-CodePudding

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.

CodePudding user response：

Merge the table on top of itself using the manager ID column on top matched to the employee ID column on bottom. Expand location using arrows atop column. Add column custom column that says

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

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"

CodePudding user response：

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