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

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.