cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smpa01 Senior Member
Senior Member

Lookup based on multiple criteria

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.

 

Sheet1.JPGSheet 1Sheet2.JPGSheet 2Sheet3.JPGDesired

 

Thank you in advance

 

 

 

4 ACCEPTED SOLUTIONS

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Lookup based on multiple criteria

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"

 

Specializing in Power Query Formula Language (M)

View solution in original post

MarcelBeug Super Contributor
Super Contributor

Re: Lookup based on multiple criteria

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.

 

Lookup based on multiple criteria.png

 

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"
Specializing in Power Query Formula Language (M)

View solution in original post

MarcelBeug Super Contributor
Super Contributor

Re: Lookup based on multiple criteria

You didn't test with my last code.

 

These are the results I get:

 

Lookup based on multiple criteria - 3.png

 

From the code in this post:

 

Lookup based on multiple criteria - 4.png

 

Specializing in Power Query Formula Language (M)

View solution in original post

MarcelBeug Super Contributor
Super Contributor

Re: Lookup based on multiple criteria

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.

Specializing in Power Query Formula Language (M)

View solution in original post

15 REPLIES 15
MarcelBeug Super Contributor
Super Contributor

Re: Lookup based on multiple criteria

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"

 

Specializing in Power Query Formula Language (M)

View solution in original post

smpa01 Senior Member
Senior Member

Re: Lookup based on multiple criteria

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...:)

 

 

smpa01 Senior Member
Senior Member

Re: Lookup based on multiple criteria

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.

 

Capture1.JPG

 

 

 

Capture2.JPGCapture3.JPG

 

 

MarcelBeug Super Contributor
Super Contributor

Re: Lookup based on multiple criteria

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?

 

Specializing in Power Query Formula Language (M)
MarcelBeug Super Contributor
Super Contributor

Re: Lookup based on multiple criteria

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?

Specializing in Power Query Formula Language (M)
MarcelBeug Super Contributor
Super Contributor

Re: Lookup based on multiple criteria

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.

 

Lookup based on multiple criteria.png

 

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"
Specializing in Power Query Formula Language (M)

View solution in original post

smpa01 Senior Member
Senior Member

Re: Lookup based on multiple criteria

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 elaborateCapture4.JPGCapture5.JPG

 

 

MarcelBeug Super Contributor
Super Contributor

Re: Lookup based on multiple criteria

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

 

Lookup based on multiple criteria - 2.png

Specializing in Power Query Formula Language (M)
smpa01 Senior Member
Senior Member

Re: Lookup based on multiple criteria

 

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.

 

Capture1.JPG

Capture2.JPG

 

 

 

 

 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)