1:1 relationship not working for identical columns of data

[kwpbi]

Hi,

I have the following columns of data in two different tables:

Workcenter Name

ASSEMBLY

BREAKFORM

BUILDING B

BUILDING B ASSEMBLY

CELL 1

CELL 2

CELL 3

CELL 4

CELL 5

CELL 6

CHIRON

CMM INSPECTION

CPP Cell

Deburr Dept.

Engineering / Programming

EXOTIC METAL CELL

Fabrication

FADAL

HAAS

INSPECTION

MAG (BUILDING B)

Maintenance

MANUALS

MH

Mills

N/C PUNCH

NHX 5500

NPI

Packaging / Inventory / Shipping

Planning

Quality

Sawing Center, (material prep.)

SHEAR

SHEET METAL

STAND ALONE CELL

TOOL CRIB

TOOL PREPARATION AND SETUP

TURNING CENTER CELL

VERTICAL MACHINE CELL

Net-Inspect Work Centers

ASSEMBLY

BREAKFORM

BUILDING B

BUILDING B ASSEMBLY

CELL 1

CELL 2

CELL 3

CELL 4

CELL 5

CELL 6

CHIRON

CMM INSPECTION

CPP Cell

Deburr Dept.

Engineering / Programming

EXOTIC METAL CELL

Fabrication

FADAL

HAAS

INSPECTION

MAG (BUILDING B)

Maintenance

MANUALS

MH

Mills

N/C PUNCH

NHX 5500

NPI

Packaging / Inventory / Shipping

Planning

Quality

Sawing Center, (material prep.)

SHEAR

SHEET METAL

STAND ALONE CELL

TOOL CRIB

TOOL PREPARATION AND SETUP

TURNING CENTER CELL

VERTICAL MACHINE CELL

I need the relationship to be 1:1 between these. I can't figure out what is wrong. They are all formatted as text and comparison in Excel yields no differences between any of the data. It wants to make it 1:Many, and then it will not let me use the RELATED() function. Please help!

[Anonymous]

You need to look at these in Power Query, not excel. There could be blanks being imported, or trailing/leading spaces, etc.  

Also, try not to make relationships on text fields, use primary and foreign keys. 

[kwpbi]

Thanks Nick, that sounds nice but I do not see anything called "Power Query" and I am not sure how to use it or what it will do for me.

I don't know what you are referring to with those keys either. I have made many 1:1 relationships in this project thus far with good results. Maybe this is only an issue with more volatile or inconsistent data?

[Anonymous]

In report or data view:

That's the ETL tool built-in. Going over what power query is and what it can do is well out of the scope of a post or two. I'd start with this link from microsoft

https://support.office.com/en-us/article/power-query-overview-and-learning-ed614c81-4b00-4291-bd3a-5...

[kwpbi]

I guess I just didn't know it was called "Power Query". I am certainly very familiar with creating and editing queries.

I see now when I enter the editor that it does say "Power Query Editor" really tiny up at the top of the window.

Now that we have resolved this issue of semanitcs, let's try to continue.

I went back through the data trail to troubleshoot the issue. For some reason, when I copy the column from Power BI, paste into Excel, and then import that data back into Power BI, it will not create a 1:1 relationship with the original data. So somehow, running that Power BI data through Excel and back is changing it somehow.

[v-chuncz-msft · Accepted solution]

@kwpbi 

You may check the posts below.

• https://community.powerbi.com/t5/Desktop/Issue-with-Data/td-p/330446
• https://community.powerbi.com/t5/Desktop/Key-value-in-lowercase-automatically-converts-to-uppercase/...
• https://community.powerbi.com/t5/Desktop/Generating-unique-keys-from-case-sensitive-data-in-Power-BI...