"Looking Up" two columns to give a result in Power Query

[Christof]

I was wondering if someone could help me figure out how to solve a query in PowerPivot, where I want to look at the outcome of 2 fields, and based on the two items returned, create a category.

From my Data Model on the FlightsTable I am trying to identify what category a flight falls under

• UK to another country = “Main Outbound”
• Another Country to UK = “Main Inbound”
• UK to UK = “UK Internal”
• Another Country to Another Country = “Non-UK Internal”

In order to do this I need to see what Country the From_City and To_City fall under on my FlightsTable. I can identify this from my AirportsTable (LHR = London Heathrow, which is United Kingdom. LVS = Las Vegas, which is in the United States).
So as an example

From_City	To_City
LHR	LVS
LHR	MAN

The first row is LHR to LVS, So United Kingdom to United States, so should be displayed in my Custom Column as “Main Outbound”.
The second row is LHR to MAN (Manchester Airport), So United Kingdom to United Kingdom, so should be displayed in my Custom Column as “UK Internal”.


On the flights table you will see that I have very crudely written some classic excel formulas in the last 5 columns to give me an answer, but I’m sure there is a way of doing this via Power Query or DAX or something, I just need to know how I go about this as someone who is trying to transition away from a load of spreadsheets linked by lookups. The actual complete file is significantly bigger, and vlookups made it very slow.


I have attached my example file.

Can anyone help.

Many thanks
Chris

 

[Chihiro]

There are many methods.

First method: Two dimension table, using DAX.
Simple to manage and construct, faster load of query, bit of performance hit on DAX.
1. In Query Editor. Right click on AirportTable and copy and paste.
2. Rename each AirportTable, dimFrom and dimTo respectively. Load both to data model.
3. Create relationship between dimFrom(Code) to FlightTable[From_City] and dimTo(Code) to FlightTable[To_City]
4. Add calculated column [Leg] to FlightTable.

=
IF (
    AND (
        RELATED ( dimFrom[Country] ) = "United Kingdom",
        RELATED ( dimTo[Country] ) = "United Kingdom"
    ),
    "UK Internal",
    IF (
        RELATED ( dimFrom[Country] ) = "United Kingdom",
        "Main Outbound",
        IF ( RELATED ( dimTo[Country] ) = "United Kingdom", "Main Inbound", "Non-Uk" )
    )
)

Second method: Merge (Left Outer Join) AirportTable twice to FlightTable in PowerQuery
Also simple to manage and construct, slower load of query, no performance hit in DAX.
1. First merge AirportTable using [From_City] and (Code) as key, then again using [To_City] and (Code).
2. Expand first joined table, only selecting [Country] and rename [FromCountry]. Do the same for 2nd joined table and rename [ToCountry].
3. Add custom column [Leg]

= if [FromCountry] = "United Kingdom" and [ToCountry] = "United Kingdom" then "UK Internal" else
if [FromCountry] = "United Kingdom" then "Main Outbound" else
if [ToCountry] = "United Kingdom" then "Main Inbound" else "Non-UK"

Third method is to use USERRELATIONSHIP. But, this is a bit more complex and has little benefit in this case to warrant it's use. There are other methods, but these three should be your top choice.

Note: [] around Code column has been replaced with () to avoid confusion with Code tag used in the forum.

EDIT: Oh, you should be able to use SWITCH() rather than nested IF.

 

[Christof]

That's amazing!! I went with option 1 and it has worked a treat!
Thank you