Power Query | Error on merging 2 tables

[trang_000]

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

[Anonymous]

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

[lbendlin]

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.

[trang_000]

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