create new column to divide values by specific row value

I want to take a source data set, filter it based on some criteria, then do a calculation on each value (dividing each gpt_date’s value by the 2nd gpt_date’s value). The expected output is a new table via blank query that outputs the new calculated values for each gpt_date. For context, this is taking daily discount factors from an interest rate curve, and rolling them forward one business day in time. It’s a common usage for financial valuations.

I have been attempting to build this in my Power BI report through Power Query, but have been struggling to get the code to work (still very new at Power Query M). I would greatly appreciate assistance as hopefully the example will give me understanding of how to do similar code in the future.

You can see the below actions I take on the initial data set. I first need to filter for the right value to use per gpt_date by taking the maximum Value for each gpt_date set. Then, I want to divide each of these Values by the 2nd gpt_date’s Value (in this case, 10/28/2024 gpt_date). The last step is to output this new data set into a blank query, excluding 10/25/2025 gpt_date as it’s no longer applicable.

The output from my query doesn’t match exactly what you specified in your request, but I’ve added comments to the code to make it easy for you to adjust if my code doesn’t fully meet your needs:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"gpt_date", type datetime}, {"Value", type number}}),

    // Group by 'gpt_date' and calculate the Max (or Min) for each date - chjange as required
    GroupedData = Table.Group(ChangedType, {"gpt_date"}, 
        {
            {"Value", each List.Min([Value]), type number}
        }
    ),

 // Add Index Column starting from 1
    AddIndex = Table.AddIndexColumn(GroupedData, "Index", 1, 1, Int64.Type),

    // Add Previous Value column, offset by one row
    AddPreviousValue = Table.AddColumn(AddIndex, "Previous Value", 
        each if [Index] = 1 then null else AddIndex[Value]{[Index] - 2}),
    
    // Calculate the Ratio (Min Value / Previous Min Value)
    AddRatio = Table.AddColumn(AddPreviousValue, "Value_rolled", 
        each if [Previous Value] = null then null else [Value] / [Previous Value], 
        type number),
    
    // Remove Index and Previous Min Value columns if they are not needed
    RemoveExtraColumns = Table.RemoveColumns(AddRatio,{"Index", "Previous Value", "Value"})
    
in
    RemoveExtraColumns