Lookup based on multiple criteria

[smpa01]

Hi,

I have this problem and if someone can please help me solve this would be great.

Sheet 1 contains a table for Managers and Sheet 2 contains a table for Allocation. I need POWER BI to assign the Header BU based on the following criteria

A. If the manager remains same even if the State changes the HEADER BU will be the first Business Unit from column A (Sheet 1) found against that particular manager or else the HEADER BU will be the first Business Unit from column A (Sheet 1) found against that particular manager

B. If the State remains same but the count of Mangers of a particular STATE is more than 1 then the HEADER BU will be picked up from the Sheet 2 corresponding to that State.

Sheet 1Sheet 2Desired

Thank you in advance

[MarcelBeug · Accepted solution]

Solution in the Power Query part of Power BI, accessible via the Query Editor.

You can paste the code below in the Advanced Editor.

From viewpoint of performance, it is best to have all required data in the same row before adding the custom column:

With input in tables Managers and Allocation, you need:

1. The Managers table merged with itself to get the previous manager/business unit on the same row as the current manager/business unit.
2. Group by State to determine if there is more than 1.
3. A merge with Allocation to get its Business Unit in case the count > 1.

let
    Source = Managers,

    // Steps to merge the table with itself to get the previous manager on the same row as the manager
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Business Unit", "Manager"}, {"Previous.Business Unit", "Previous.Manager"}),

    // Group by State to determine in there is more than 1.
    #"Grouped Rows" = Table.Group(#"Expanded Previous", {"State"}, {{"Count", each Table.RowCount(_), type number}, {"AllData", each _, type table}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Business Unit", "Manager", "Index", "Index.1", "Previous.Business Unit", "Previous.Manager"}, {"Business Unit", "Manager", "Index", "Index.1", "Previous.Business Unit", "Previous.Manager"}),

    // Merge with Allocation to get its Business Unit in case the count > 1.
    #"Merged Queries1" = Table.NestedJoin(#"Expanded AllData",{"State"},Allocation,{"State"},"Allocation",JoinKind.LeftOuter),
    #"Expanded Allocation" = Table.ExpandTableColumn(#"Merged Queries1", "Allocation", {"Business Unit"}, {"Allocation.Business Unit"}),

    // Now add the custom column
    #"Added Custom" = Table.AddColumn(#"Expanded Allocation", "Header BU", each if [Manager] = [Previous.Manager] then [Previous.Business Unit] else if [Count] > 1 then [Allocation.Business Unit] else [Business Unit]),

    // Finishing touches:
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Index.1", "Previous.Business Unit", "Previous.Manager", "Allocation.Business Unit"})
in
    #"Removed Columns"

[MarcelBeug · Accepted solution]

Another suggestion, that returns the results from your examples, would be: if a state has more than 1 distinct managers, it gets the business unit from sheet2, otherwise it gets the first business unit for this manager.

let
    Source = Managers,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Manager"}, {{"First BU", each List.Min([Business Unit]), type number}, {"AllData", each _, type table}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Business Unit", "State", "Index"}, {"Business Unit", "State", "Index"}),
    #"Grouped Rows1" = Table.Group(#"Expanded AllData", {"State"}, {{"AllData", each _, type table}, {"Distinct Mgrs per State", each List.Count(List.Distinct([Manager])), type text}}),
    #"Expanded AllData1" = Table.ExpandTableColumn(#"Grouped Rows1", "AllData", {"Manager", "First BU", "Business Unit", "Index"}, {"Manager", "First BU", "Business Unit", "Index"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded AllData1",{"State"},Allocation,{"State"},"Allocation",JoinKind.LeftOuter),
    #"Expanded Allocation" = Table.ExpandTableColumn(#"Merged Queries", "Allocation", {"Business Unit"}, {"Allocation.Business Unit"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Allocation", "Header BU", each if [Distinct Mgrs per State] = 1 then [First BU] else [Allocation.Business Unit] ),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Business Unit", "Manager", "State", "Header BU"})
in
    #"Removed Other Columns"

[MarcelBeug · Accepted solution]

You didn't test with my last code.

These are the results I get:

From the code in this post: