cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DAXRichArd Member
Member

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

 

 

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

 

ASM Template.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
DAXRichArd Member
Member

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

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

2 REPLIES 2
Community Support Team
Community Support Team

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

@DAXRichArd,

 

You may refer to the following post.

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DAXRichArd Member
Member

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

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 288 members 3,075 guests
Please welcome our newest community members: