How to replicate if and vlookup in power query?

Hi - I am trying to replicating my current analysis in power query and appreciate some help.

In my excel analysis, I have some original data as below. Then I have three steps to create a desired view

1) vlookup table to get the direction (north, east, west)

2) IF statements and vlookup (if it is "banana", then "banana", if it is "channel marketing", then "channel marketing", vlookup another table)

3) Final conditional logic to determine the category(outcome).

Questions:
1) How do you write the vlookup formula in power query?
2) How do you write the IF statements and vlookup together? My real ifs are lot longer than this example
3) Is there anyway to make my three steps simpler in power query?


I am attaching the excel file for your review. I really appreciate any help you can provide. Thank you!

Have a look at the attached.

Power query - vlookup, if and vlookup

Comfy said:

Have a look at the attached.

Click to expand...

Comfy - thanks for taking the time to share some tips. It looks really good. Out of desperation I reached out to Microsoft professional service team yesterday and somehow got to a similar place. The only difference is that I am using a customized column for the last step as I don't how to write the magic formulas as you did.

One question - I like the merge function.. My actual data set is huge, after doing two merges, power query becomes very slow. Is it better to use data model relationships instead? if so, how will the formula change then? In this kind of situation, what would be the best practice to get this results? One way I might do is to cut down the data I bring in, the downside is this will require an upfront manual step.

Any thoughts on this?

Thanks again for your help, much appreciated.

No idea I'm afraid.

I don't have a version or PC that allows the use of the Data model/Power Pivot.