Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
kwpbi
Helper II
Helper II

1:1 relationship not working for identical columns of data

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!

1 ACCEPTED SOLUTION

5 REPLIES 5
Anonymous
Not applicable

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. 

Capture.PNG

 

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

In report or data view:

Where PQ is.png

 

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

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.

@kwpbi 

 

You may check the posts below.

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.