Forum Discussion

llgoodmond's avatar
llgoodmond
Regular Visitor
1 year ago
Solved

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

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:

IDBusiness_IDBusiness_Alt_IDBusiness_Name
11000010000Apricot
21000110001Raisin
31111111113Red Delicious
41111211113McIntosh Red
51111311113Granny 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!

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.