Wrong one-to-many relationship direction

[cleverclog]

Hello,
I have an issue and I desperately need your help. I have two tables with orders (a trasaction number is a unique value) and another table with sales invoices (several invoies can be issued to one transaction number). The tables are linked via transaction numer, however when I create a relationship between these two, Power Query sets a relationship as if the table qith uniques contained multiple values and the other way round. I have tried all methods of creating relationships and it will always end up the same way. Do you have any hints what may be wrong?

Just to add some context as it may be helpful. These are large sets of data imported directly from a database through ODBC into Power query and then sent into a data model.

Can anyone please help?

 

[gazpage]

Screenshot pls.

This should be impossible. Do a count and a distinct count on the columns in the relationship to check.

 

[macfuller]

When I moved to 2016 I had something like this where the relationship was based on a date field. I had created the relationship by dragging fields in the graphic window. I deleted the relationship and built it in the relationship tool making sure to put the unique lookup table first.
Maybe you've already tried that and failed, but I don't have another recommendation. My guess is that since 2016 allows multi-direction relationships that sometimes it might get confused?

 

[Jerry Sullivan]

I encountered a similar problem recently while trying to use PowerPivot to make a relationship between 2 tables created using PowerQuery. I was able to achieve the result I wanted using the Merge feature in Power Query instead of doing that within the diagram view of PowerPivot.

Ken Puls has an example here.

https://www.excelguru.ca/blog/2015/12/16/merge-tables-using-outer-joins-in-power-query/