79100358

Date: 2024-10-18 02:43:51
Score: 0.5
Natty:
Report link

Try to use and modify this code apply to your case, if it's any help. Replace Table1 in the first line with the name of your source table.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

// Group by CurveName GroupedByCurve = Table.Group(Source, {"CurveName"}, { {"Data", each _, type table [CurveName=text, BusinessDays=number, InterestRate=number]}, {"MinDay", each List.Min([BusinessDays]), type number}, {"MaxDay", each List.Max([BusinessDays]), type number} }), // Generate interpolated data for each curve InterpolatedData = Table.AddColumn(GroupedByCurve, "InterpolatedData", each let AllDays = {[MinDay]..[MaxDay]}, Interpolate = (day) => let SortedData = Table.Sort([Data], {{"BusinessDays", Order.Ascending}}), PrevRow = Table.SelectRows(SortedData, each [BusinessDays] <= day), NextRow = Table.SelectRows(SortedData, each [BusinessDays] > day), Prev = Table.LastN(PrevRow, 1), Next = Table.FirstN(NextRow, 1), x1 = Prev[BusinessDays]{0}, y1 = Prev[InterestRate]{0}, x2 = Next[BusinessDays]{0}, y2 = Next[InterestRate]{0}, InterpolatedRate = y1 + (y2 - y1) * (day - x1) / (x2 - x1) in [BusinessDays = day, InterestRate = InterpolatedRate] in List.Transform(AllDays, Interpolate) ), // Expand the interpolated data ExpandedData = Table.ExpandListColumn(InterpolatedData, "InterpolatedData"), FinalResult = Table.ExpandRecordColumn(ExpandedData, "InterpolatedData", {"BusinessDays", "InterestRate"}, {"BusinessDays", "InterestRate"}), // Remove unnecessary columns and sort the result CleanedResult = Table.RemoveColumns(FinalResult, {"Data", "MinDay", "MaxDay"}), SortedResult = Table.Sort(CleanedResult, {{"CurveName", Order.Ascending}, {"BusinessDays", Order.Ascending}})

in SortedResult

Reasons:
  • Blacklisted phrase (1): any help
  • Long answer (-1):
  • Has code block (-0.5):
  • Low reputation (1):
Posted by: vmhung