Hi Team,
I am trying to upload the latest rate changes and Tax Code as per attached file.
When the CustID, BCD and SegNum are the same, we will have to pick the rate for latest EffDt(Effective Date). If they are not the same, we will pick up the entire line as it is.
And we will change TaxCD(TaxCode) to EX-BAS.
Please see attached for reference.
Thank you.
Regards,
Aye
Hi Aye,
You should sort the EffDt column descending, then select first 4 columns and remove duplicates, this will remove the duplicates with older dates.
Make sure you add to the sort step the Table.Buffer, otherwise the remove duplicates step will not work as expected.
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"CustID", type text}, {"BCD", type text}, {"SegNum", type text}, {"BilltoDT", type datetime}, {"EffDT", type datetime}, {"TaxCD", type text}, {"Inactive", type logical}, {"Rate", type number}, {"Per", type text}}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"EffDT", Order.Descending}})),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Company", "CustID", "BCD", "SegNum"})
in
#"Removed Duplicates"
You can replace now the tax code you wanted (use replace value, or just delete that column and add it back with this formula:
="New Tax Code"
Thank you so much, Catalin.
This is exactly what I want. It is nice and simple.
Regards,
Aye
