Cannot set Many to One relationship

[Anonymous]

Hi,

I've searched for answers, but I just cannot resolve this problem.  I built a model in PBI Desktop.  If you look at my 'Relationships  Oct 2022' screenshot, you'll see the relationships I set.  It worked.  That was my initial demo build.

Now I am trying to expand that model with a much larger dataset in my build sheet.  The Build Sheet (attached) Placement ID column is the column that's supposed to have unique values.  If you look at my model (pbix file attached) or the 'power query' screenshot, you'll see I set queries to remove: blank rows, errors, and duplicates.  I also set Placement ID column to UPPERCASE TEXT in all the tables (except the Calculated-Costs-Table).  When I hit apply in Power Query, I can see the number of rows are reduced, so you'd assume it's working the way you'd expect.

However, when I go to create relationships, it won't let me set Many-to-One relationship between Calculated-Costs-Table  and the Build Sheet table.  Please see attached Relationships screnshot to see wat I'm trying to do.

Please help me understand how I can resolve this in PBI.  I do not want to manually clean the source data.

Is there a way to set Placement ID column to UPERCASE TEXT format in the Calculated-Costs-Table?  And do you think this would help?  How would I do this in the exisiting DAX code of that table?

build sheet: https://we.tl/t-0OuQpszNLQ

PBIX file:  https://we.tl/t-xRJKr9nKCn

power queryRelationships

Thank-you

[lukiz84 · Accepted solution]

1) What if you load the Placement ID into a table? Before any relationships, just to check if there are really no duplicates?

2) Why did you Uppercase them? They are just numbers?

[lukiz84]

Maybe the "empty rows" had some spaces? 🙂 

[Anonymous]

I doubt that because I'd already gone into my build sheet excel previously, highlighted all rows, and used find and replace to get rid of spaces

[Anonymous · Accepted solution]

thank-you @lukiz84 

I gave up trying to do this quickly via Power BI's transform data, remove blanks, remove errors, remove duplicates commands (because they do not work in my example).

Instead, I went back into my source data, the build sheet Excel, used conditional formatting to highlight duplicates and I manually removed duplicates, one by one.  Then next, removed blanks.  Cleaned my source data the slow way.  I had hoped PBI would deal with this quickly for me.

Later, my co worker tells me that in PBI Power Query, the remove duplicates query worked for him.  It is the 'Remove Blank rows' query that doesn't work. He said quick workaround is to select the unique ID's column, hit the drop down selector on the header, now simply untick blanks to filter them out.  It was that simple!  Worked for him, then you are able to move on and set that many-to-One relationship between tables.

oh and you asked why I made it UPPER CASE. All my Id's are already in Text format; that's how they appear in the source data.  I made them UPPER CASE in PBI just to ensure all ID columns in all the tables are the same format.

[lukiz84 · Accepted solution]

1) What if you load the Placement ID into a table? Before any relationships, just to check if there are really no duplicates?

2) Why did you Uppercase them? They are just numbers?