How to Count Airport Codes from 2 columns

[DiogoPS]

Hi team,

My first post here. I'm new to the PowerBI world and I'm facing a challenge that I hope that should be simple to execute.

I have a spreadsheet that I exported from my FlightDiary with 2 colums; Origin and Destination. These 2 columns have Airports codes (for example: JFK, BOS, LAX, GRU, MCO,..)

What I want to do is to create a visual where I have the full list of airports and the total number of trips. For example:

In this picture, I want to have the aiport GIG showing 4 and BEL showing a total of 3 trips (among origins and destinations). So, basically I want to count the aiport codes within 2 columns.

I even created another table with the aiport code lookup to stablish a relation between both tables, but I can only connect 1 column from one table to 1 column from another table...

Is there a best and esasy way to make this happen?

[ryan_mayu · Accepted solution]

@DiogoPS 

should GIG be 5 based on the sample data you provided?

1. create an index column in pq

2. select index column and unpivot other columns

then you can drag the columns to visual to get what you want

[DiogoPS]

It worked! Thanks a lot!

[ryan_mayu]

you are welcome