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
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
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
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