complex fomula transfer from Excel to Power BI edit query

[engingee]

Hi everyone, 

I currently have this formula in Excel and would like to wirte it in Power BI and give me the colunm result, I have tried condition column in query editor, but it it not working, can everyone help please, thanks in advance, the fomula (logic) is showing below, 

Firstly, I have 2 tabs, in Excel:

Tab1

Tab2

Formula in excel

=IFERROR(IFS(

Tab1 column A="one",VLOOKUP(Tab1 column B , 'Tab2'!$B$5:$C$75,2,FALSE),

Tab1 column A ="two",VLOOKUP(Tab1 column B, 'Tab2'!$B$76:$C$125,2,FALSE),

Tab1 column A ="three",VLOOKUP(Tab1 column B, 'Tab2'!$B$126:$C$149,2,FALSE),

Tab1 column A ="four",VLOOKUP(Tab1 column B,'Tab2'!$B$150:$C$191,2,FALSE)),

" Not Categorized")

[aj1973]

Hi @engingee 

You wouldn't need to use VLOOKUP in Power BI once you import your 2 Tabs. The relationship between the 2 Tabs will be created in Power BI and you will be able to add your column or your Pivot table. 

[engingee]

thanks for your answer, but still a bit confused because both Tab1 column A and colunm B, and Tab 2 colunm B and colunm C have duplicated values🤔

[v-easonf-msft · Accepted solution]

Hi, @engingee 

It seems that you want to search for the corresponding value in the four areas of Tab2.

If so, you can first split  Tab2 into 4 separate tables (Tab2.1,Tab2.2,Tab2.3,Tab2.4).
(Select the range in excel->Create table->rename table name.
Newly created tables can  be found in the 'Navigation' when you connect to the excel file)

Then you can create a calculated column in Tab1 as below:
Dax:

Result =
IFERROR (
    SWITCH (
        Tab1[ColumnA],
        "one", LOOKUPVALUE ( Tab2.1[ColumnC], Table2.1[ColumnB], Tab1[ColumnB] ),
        "two", LOOKUPVALUE ( Table2.2[ColumnC], Table2.2[ColumnB], Tab1[ColumnB] ),
        "three", LOOKUPVALUE ( Table2.3[ColumnC], Table2.3[ColumnB], Tab1[ColumnB] ),
        "four", LOOKUPVALUE ( Table2.4[ColumnC], Table2.4[ColumnB], Tab1[ColumnB] )
    ),
    "Not category"
)

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[engingee]

Hi @v-easonf-msft ,

Thanks for you answer, I just wondering if that any ways I can write your calculate colunms DAX into Custom Column in Power Query Editor please? Many thanks!

[v-easonf-msft]

Hi, @engingee 

Dax and M query are not similar. It can be difficulte to convert DAX into custom column.

Please share the data of your two tables so that I can make a sample for your reference.

Best Regards,
Community Support Team _ Eason

[engingee]

Hi @v-easonf-msft thanks for you explain, I think I better try to work out as DAX as you suggested at this stage, and then the custom colunm.

[aj1973]

I see and I think you will need to create a bridge table containing column B only without duplicates then relate all 3 Tables together through 1 to many relationships