Forum Discussion
Looking for not equal rows
Hello,
I have two tables from the same source. The tables consist of machine data, for example there are OrderID's and the Datetime when the machine finished packing it. Unfortunately the leading primary key is not distinct, because the machine is reusing the same ID's after every few month. So I combined it in PowerQuery Editor with the Datetime to make a kind of composite key column, so now every entry is distinct. Since the first table has more then one entry per orderID I duplicate that and create a second grouped table where the entry is just existing one time. For simplicity reason I just add a date column not a datetime column.
Table 1:
| OrderID | Action | Datetime | CompositeKey | |
| 1 | Fetch | 2021-05-01 | 101.05.2021 | |
| 1 | Pack | 2021-05-02 | 102.05.2021 | |
| 1 | Finish | 2021-05-03 | 103.05.2021 | |
| 2 | Fetch | 2021-05-01 | 201.05.2021 | |
| 2 | Pack | 2021-05-02 | 202.05.2021 | |
| 2 | Finish | 2021-05-03 | 203.05.2021 |
Table2:
| OrderID | Datetime | CompositeKey | ||
| 1 | 2021-05-01 | 101.05.2021 | ||
| 2 | 2021-05-01 | 201.05.2021 |
Seemingly I should have a n:1 one relationship. My purpose is to use table 2 for better filtering and selection options, since every entry is distinct. But in the datamodel it says its a m:n relationship and I'm surprised. As far I know already one wrong row can transform it into m:n cardinality. Is there a way to quickly find the regarding rows, where I have more then 1 row of that entry in the second table? I hope someone knows how to find them.
Thank you very much in advance.
Best.
Are you using the Composite key to create the relationship? If so, try making it a whole number instead (101052021, 102052021...)
3 Replies
- PaulDBrownCommunity Champion
Are you using the Composite key to create the relationship? If so, try making it a whole number instead (101052021, 102052021...)
- Applicable88Impactful Individual
Very good idea. Thanks PaulDBrown . After replacing all punctuation in the date with space and then combined the column with the order id I got a distinct primary key as a whole number to work with.
- CNENFRNLCommunity Champion
Little useful info can be obtained from gibberish. One thing is for sure, PBI isn't traditional RDBMS; concepts such as composite key, foreign key aren't mandatory in modeling; whereas relationships and filtering matter.