Forum Discussion
Power Query | Error on merging 2 tables
Actual Case:
Table 1:
- Original data:
| PolNo | Tranno | IncDate | ExpDate | EffDate |
| 12345 | 1 | 20241201 | 20241231 | 20241201 |
| 12345 | 2 | 20241201 | 20241231 | 20241202 |
| 12345 | 3 | 20241201 | 20250115 | 20241203 |
- Step 1: Add "Merged" column = PolNo+EffDate+Tranno
- Step 2: Sort "Merged" desc
- Step 3: Add "Merged.1" column = PolNo+IncDate
- Step 4: Deduplicate "Merged.1"
Table 2:
- Original Data
| PolNo | EffDate |
| 12345 | 20241204 |
- Step 1: Merge Table 1 by PolNo
- Step 2: Expand Table1.IncDate and Table1.ExpDate
Actual Result
- Table1.ExpDate = 20241231
Expected Result:
- Table1.ExpDate = 20250115
3 Replies
What's your reason for sorting in step 2?
Which columns are you deduplicating by in step 4? Note that Power Query "Remove Duplicates" is more often than not doing something different than what you expect.
Thanks for your feedback.
- Step 2 - 4: to get transaction has the max EffDate and max Tranno.
- Deduplicate means removing duplicate. I removed duplicate in column Merged.1 = PolNo + IncDate
Hi trang_000
Can you provide some more sample data, the sample data you provide is relatively small that can't restore the expected results you want.
Best Regards!
Yolo Zhu
