Create Relationships You can't create a relationship between these two columns

[DAXRichArd]

Attached is a screen shot of my model.

Error Message:

"Create Relationships

You can't create a relationship between these two columns because one of the columns must have unique values."

Background

I work with commercial aviation data.

I work with data from several sources. I wish to align and run calculations off 5 distinct groups of files. 4 of those five groups have 19 files each. That is to say my data source is based on greater that 76 files (4 x 19 = 76).

Groups of Files

Pax = Passenger data

Cargo = air cargo data

Landings = airport landings by airline

FAA Operations = FAA operations report

Schedule Dynamic Table = commercial airline schedule data

Data Quality

Excluding the schedule data and FAA Operations, everything else is a mess.

Query

I've brought in all my data and have appended pax files to pax, cargo to cargo, et cetera. All is working well.

Problem

Most files have an airline name column in common (and date). The names are a mess. For example, United Airlines appears as United, United Airlines, United Air Lines, United Airlines, Inc (no period and with period, with common and no period, et cetera). I have approximately 350 unique airline names.

Desired Outcome

I want to create relationships with the airline names to all the groupings and run calculations. For example, I want to run a load factor calcution. I divide the number of seats (capacity) to how many passengers are on the flight. It tells me what percent of the plane is full.

Attempted Resolutions

I've tried creating 'helper tables' using functions DISTINCT and SELECTCOLUMNS, or INTERSECT and DISTINCT. I created an 'Airline' column in each file using SWITCH to clean up the names then tried creating relationships between these. I use the query feature, and created a table by appending all tables, then removing all columns except the airline name in order to create a table of airline names only, then clean up the names using the SWITCH function. All my attempts result in the error message I post at the top this message.

Goal

I want to use the airline names as the unique identifier to run calculations and analaysis on various airline data. 

Your help is greatly appreciated.

Richard

Houston, Texas

[v-chuncz-msft]

@DAXRichArd,

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/Unique-values-from-two-columns/m-p/468317#M217488

[DAXRichArd · Accepted solution]

Hi Sam,

Thanks so much for your reply. When I have a moment I'll give your solution a try. I've started a work around. I created a separate excel file and dumped all the airline names there, brought that file in with a query and have been working with that. The relationships are working (my habit is to test my work as I go).

Big thank you. Hope to circle back with you soon with an update.

DAXRichard