Many to relation error

[shemajuc]

How to solve below error

''Column 'jntkey' in Table 'vw_pmsjntd_summ' contains a duplicate value 'EP-146 048 MW065' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.''

My view

create  view [dbo].[vw_pmsjntd_summ]
as
select distinct d.welder,h.kp,h.kp_no,h.refkey,h.weldrepno,h.welddate,h.rt1,
case when h.rt1dt>='01/01/2015' then h.rt1dt else null end as rt1dt,
h.rtpassed,h.sortfld1,h.inchdia,h.stagno,h.etagno,wps,
d.refkey+d.welder as jntkey,
case when d.welddate>='01/01/2015' then 1 else 0 end as welded,
case when d.welddate>='01/01/2015' and rt1dt>='01/01/2015' then 1 else 0 end as tested,
case when d.welddate>='01/01/2015' and rt1dt>='01/01/2015' and rtpassed=1 then 1 else 0 end as accepted,
case when d.welddate>='01/01/2015' and rt1dt>='01/01/2015' and rtpassed=0 and sortfld1 like '%RS%' then 1 else 0 end as reshoot,
case when d.welddate>='01/01/2015' and rt1dt>='01/01/2015' and rtpassed=0 and sortfld1 not like '%RS%' then 1 else 0 end as rejected,
w.name,w.badgeno,cast(w.qualified as nvarchar(max)) as qualified,
case when reldate>='01/01/2015' then reldate else null end as reldate,
case when w.welder like 'C%' then 'AUTOMATIC' ELSE 'MANUAL' end AS welder_type
from vw_pmsjntd as d left join vw_pmsjnth as h on d.refkey=h.refkey
left join vw_pmswldr as w on d.welder=w.welder
where h.welddate>='01/01/2015' and h.weld_no not like '%R%' and h.weld_no not like '%CAS%' and h.isono not like 'MW%' AND ( H.ISONO <> 'EP-156'AND H.weld_no ='007' )

this is my relation

Error is below

''Column 'jntkey' in Table 'vw_pmsjntd_summ' contains a duplicate value 'EP-146 048 MW065' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.''

How to solve ?

[vanessafvg]

@shemajuc you  need to have a  one to many relationship, obviously this is a many to many, so you either need to create a unique set of ids in a separte table and then link these 2 tables to the unique set of id's

[shemajuc]

i make it

then data not getting correct.....

[vanessafvg]

@shemajuc did you remove duplicates in power query?

2 issues could be happening here

1. if you have any blank id's this will create an issue, you should replace all blanks with a dummy id like -1 or something

2. power query is case sensitive, so if you removed duplicates in power query but the key has different cases like BM1 or bm1 power query will see this as different, make sure you change all to either upper or lower case 

[shemajuc]

yes i did... 

[shemajuc]

[vanessafvg]

@shemajuc yes so did you undertand what i asked you to check for?

you must change them all to one case, and you must make sure there are no blank values (on both sides)

or you must pad them with a dummy value

[vanessafvg]

@shemajuc yes you did what?