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:

[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"

[smpa01]

Hi Marcel,

To test the code I added a row as folllowing and I wanted the Header BU to be returned for that row as 105. But this code returns it as 1004.  Can you please take look.

[MarcelBeug]

Can you clarify the rules?

Especially "stays the same": is this from 1 row to another or do you mean now that:

1. If manager appears multiple times anywhere (not necessarily on consecutive rows) then you need the first business unit with that manager,
2. Otherwise (so the manager appears only once) if the state appears multiple times (not necessarily on consecutive rows) then you need the business unit from sheet2
3. Otherwise you need the business unit from the same row.

Please confirm if this is correct or else: what are the corect rules?

[MarcelBeug]

Additionally, can you clarify why the rows with manager BP and state ON shoul get 1012, and - in the new example - te rows with manager ALC and state QC should get 105?

[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"

[smpa01]

Hi Marcel,

Sorry for the late reply.

To clarify your doubts

1. If manager appears multiple times anywhere (not necessarily on consecutive rows) then you need the first business unit with that manager - For every manager the Header BU will be the Business Unit that corresponds to FM-ST intersection. If a particular manager (for example BL apperas multiple times, not necessarily on conconsecutive rows) appears multiple times (even if the ST does not remain same) the Header BU will be the first Business Unit that corresponds to FM-ST intersection. So BL-AB and BL-BC will have header BU as 100.
2. Otherwise (so the manager appears only once) if the state appears multiple times (not necessarily on consecutive rows) then you need the business unit from sheet2 - For every manager the Header BU will be the Business Unit that corresponds to FM-ST intersection (If the Manager apperas only once)
3. However, if the count of distinct manager per ST is more than one then the Header BU wil be the allocation BU from Sheet 2.

To elaborate

[MarcelBeug]

Take a look at my previous post: if I apply that simple rule with the associated code, I get the same result.

[smpa01]

Thanks for the reply.

Hoiwever, to test this code I added another row (row 11 and 12)   and it did not return the result in Header BU column I was looking for row 12. The Header BU for that row should have been 100 and not 121 since Manager remains same and the State changes. Therefore, it should assign the First Business Unit value against that Manager.

Sorry to bug you.

[MarcelBeug · Accepted solution]

You didn't test with my last code.

These are the results I get:

From the code in this post:

[smpa01]

Thanks Marcel a lot. Yes it was my Typo which did not return the desired result otherwise the code works with all the conditions.

I want to add another level to it and I will post in few days.

[smpa01]

Hi Marcel,

Hope you are well. I have added another level (Vendor) to the previous spreadsheet.

The logic is if the Distinct Manager per State-Vendor = 1 then #Min BU per Manager-Vendor else Allocation Business Unit. I followed your code and it is working fine with my requirement. It can calculate the Header BU as I desired. However, when the data loads it is generating error and I have no clue why it happens.

If I don't load the data and create only connection and use a pivot to get the connection, I don't se any error. It only occurs if

If you can please take a look and advise how to get rid of it, would be great.

https://drive.google.com/file/d/0B7imabOIHE8QTTFBLWw4aFU5dzg/view?usp=sharing

Thank you in advance.

[smpa01]

This is the error I am getting

[MarcelBeug · Accepted solution]

Can't spend more time on this. The problem is with column Distinct Mgrs per S-V in query MLT_V_FM_ST (2)

It is defined as text, while the content are numbers. This is no error for Power Query, but it is an error when data is loaded to Excel.

(as it is no error for Power Query, you don't get any error records in your error query).

So change the type to (whole) number and the issue is solved.

[smpa01]

THanks for your time. It is resolved now.

[smpa01]

Hi Marcel,

Thank you very much for providing the code.

It works !!!!

If I find a more complex condition to add I will post here.

Thank you...:)