Replicating the functionality of a nested 'INDEX - MATCH' Excel formula (M-Code or DAX)

[llgoodmond]

Hello Community,

I came up with a way to handle changing business names in Excel for reporting.  Using an alternative ID, I can match actual IDs with the business name.  The formula is a nested INDEX-MATCH.  Of course, the demand to move reporting to Power BI is strong, and PowerQuery does not have INDEX or MATCH (VLOOKUP was kind of replaced by the Merge Tables feature), nor does DAX in Power BI.  My request for help is finding a way to replicate the functionality of the Excel formulas in PowerQuery M-Code or DAX (Power BI)

--How the tables are designed:

Essentially, the highest Business_ID number corresponds to the latest Business_Name.  The formula searches for the Business_Alt_ID first, then compares that to the Business_ID numbers, and outputs the matching and latest Business_Name. 

--How the formula works:

In the image below, the formula in C18, depicted in the formula bar, uses A18 ('Red Delicious') as a lookup value to match against the Business_Name column, and return a Buisness_Alt_ID ('11113') from that Business_Name's relative position (4th row).  This Business_Alt_ID is then used as the lookup value to match against the Business_ID column, and return the latest Business_Name from that relative position (6th row).

Note that the Business_Name column is used TWICE in the formula in both INDEX-MATCH functions.  The desired outputs are described in columns D18 through D21.  If using the sample dataset to replicate in Excel, replace Banana with 'Apricot' or 'Raisin'.

BizzDat Table sample data:

ID	Business_ID	Business_Alt_ID	Business_Name
1	10000	10000	Apricot
2	10001	10001	Raisin
3	11111	11113	Red Delicious
4	11112	11113	McIntosh Red
5	11113	11113	Granny Smith

FORMULA: =INDEX( BizzDat[Business_Name], MATCH( INDEX( BizzDat[Business_Alt_ID], MATCH('cell with a business name',BizzDat[Business_Name],0)), BizzDat[Business_ID], 0))

P.S. Updated with explanations of how the table is intended to work, and how formula is obtaining the correct output, along with a small text dataset.  Thanks for the post assistance and links @Greg_Deckler!

[Anonymous · Accepted solution]

Hi, @llgoodmond ,

Thanks for Greg_Deckler's reply!
And @llgoodmond , you can try to use this M code to create a custom column in the Power Query:

if [Business_ID] = [Business_Alt_ID] then 
            [Business_Name] 
        else 
            let
                CurrentRow = [Business_Alt_ID],
                MatchingRow = Table.SelectRows(#"Changed Type", each [Business_ID] = CurrentRow)
            in
                if Table.RowCount(MatchingRow) > 0 then
                    MatchingRow{0}[Business_Name]
                else
                    null

And the final output is as below:

Here is the whole M code in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0AAIk2rGgKDM5v0QpVidayQgqbohEByVmFmfmgaWNQcIgAKVB/KDUFAWX1JzM5Mz80mKwKhOorBGSKt9kz7yS/OIMBaBysCJTJEkY7V6UmJdXqRCcm1mSoRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Business_ID = _t, Business_Alt_ID = _t, Business_Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Business_ID", Int64.Type}, {"Business_Alt_ID", Int64.Type}}),
    AddCustomColumn = Table.AddColumn(#"Changed Type", "Result", each if [Business_ID] = [Business_Alt_ID] then 
            [Business_Name] 
        else 
            let
                CurrentRow = [Business_Alt_ID],
                MatchingRow = Table.SelectRows(#"Changed Type", each [Business_ID] = CurrentRow)
            in
                if Table.RowCount(MatchingRow) > 0 then
                    MatchingRow{0}[Business_Name]
                else
                    null)
in
    AddCustomColumn

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@llgoodmond DAX has a LOOKUPVALUE function or you can use MAXX( FILTER( ... ), ... ). It's hard to tell exactly what you are trying to do however. 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.