Adding new rows and adjusting original row data

Hi bigk,

Try below code:

let
// Step 1: Load source data (already processed to AddedPrice)
Source = AddedPrice,

// Step 2: Extract Split rows and compute split ratios
SplitRows = Table.SelectRows(Source, each Text.Contains([Type], "Split")),
WithRatios = Table.AddColumn(SplitRows, "SplitRatio", each
let
raw = Text.Trim([Split details]),
parts = Text.Split(raw, ":"),
numerator = try Number.FromText(parts{0}) otherwise null,
denominator = try Number.FromText(parts{1}) otherwise null
in
if numerator <> null and denominator <> null then numerator / denominator else null,
type number),

// Step 3: Aggregate cumulative split ratio per Position ID
GroupedRatios = Table.Group(WithRatios, {"Position ID"}, {
{"TotalSplitFactor", each List.Product(List.RemoveNulls([SplitRatio])), type number}
}),

// Step 4: Join back to source to adjust Open Position rows
JoinedToMain = Table.NestedJoin(Source, {"Position ID"}, GroupedRatios, {"Position ID"}, "SplitFactor", JoinKind.LeftOuter),
ExpandedFactors = Table.ExpandTableColumn(JoinedToMain, "SplitFactor", {"TotalSplitFactor"}),
AdjustedOpen = Table.AddColumn(ExpandedFactors, "AdjustedUnits", each
if [Type] = "Open Position" and [TotalSplitFactor] <> null then
[Units] * [TotalSplitFactor]
else [Units],
type number),

// Step 5: Create synthetic rows for each split row
GeneratedSplits = Table.AddColumn(WithRatios, "SyntheticRows", each
let
originalRow = _,
positionID = originalRow[Position ID],
splitRatio = originalRow[SplitRatio],
date = originalRow[Transaction date],
baseRows = Table.SelectRows(Source, each [Position ID] = positionID and [Transaction date] < date and [Type] = "Open Position"),
unitsBefore = try baseRows{0}[Units] otherwise null,
unitsAfter = try unitsBefore * splitRatio otherwise null,
commonFields = Record.RemoveFields(originalRow, {"Type", "Units", "Amount"})
in
if unitsBefore <> null and unitsAfter <> null then {
Record.Combine({commonFields, [Type="Synthetic Split - Reverse", Units=-unitsBefore, Amount=null]}),
Record.Combine({commonFields, [Type="Synthetic Split - Apply", Units=unitsAfter, Amount=null]})
} else {}
),
FlattenedSynthetic = Table.Combine(List.Combine(GeneratedSplits[SyntheticRows])),

// Step 6: Combine and sort all rows
CombinedAll = Table.Combine({AdjustedOpen, FlattenedSynthetic}),
SortedFinal = Table.Sort(CombinedAll, {{"Transaction date", Order.Ascending}}),

// Step 7: Re-indexing and balance tracking
Reindexed = Table.AddIndexColumn(SortedFinal, "Index", 0, 1, Int64.Type),
WithIndex1 = Table.AddIndexColumn(Reindexed, "Index.1", 1, 1, Int64.Type),
MergedPrevBalance = Table.NestedJoin(WithIndex1, {"Index"}, WithIndex1, {"Index.1"}, "PrevRow", JoinKind.LeftOuter),
ExpandedPrevBalance = Table.ExpandTableColumn(MergedPrevBalance, "PrevRow", {"Balance"}, {"Previous blance"})

in
ExpandedPrevBalance

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

I ended up going off of the Source step of your source data as some of the transformation steps weren't working in my default locale.

So, given:

Original (as provided by OP)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZJBa8MwDIX/ism5cyRbkmVft9sOHWQ7hR5G1kNpacra/8/sJF1L6dZeBgODXuyHvxfJbVuB1oC1A4cGKXlOoNWsmu+WW/PS71eHVb/N383r/PG5fmuesmawALkiWnXgPWY9bZGBiBapuNgGn6sqchSkctwc+m69P9oXs7bCeE4nuJ9eivKA+IYHDNbHItFSyDWCqAuO/FW44xr8BJdEmU834Ihimcc7HJ/QakjIsisW47NJBwlBUB2hXsVDqPMa8T55TFAiH8mm2/T75cdFgCA28kXv3bSnJipZJ1kKgcV4s/slgZYE3kAYEhRf13/uzHtXQiTT7Darw3kIgwnh9OcP503IExmlKFiJ97XgbwPceAFhen5c+Bzu5MuP/DKAYfTkLMg/4P82gMUX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Type = _t, Details = _t, Amount = _t, #"Units / Contracts" = _t, #"Realized Equity Change" = _t, #"Realized Equity" = _t, Balance = _t, #"Position ID" = _t, #"Asset type" = _t, NWA = _t])
in
    Source

I think the following will provide what you want. You can see I did my own value/type transforms up front (except for Int64, explicitly declared the locale). Majority of the work occurs in the Group step. Inside the group step, a List.Generate transform is used to construct the sell/buy rows.

let
    Source = Original,
    FixValsTypes = Table.TransformColumns(
        Source,
        {
            {"Date", each DateTime.From(_, "en-IN"), type datetime},
            {"Amount", each Number.FromText(_, "en-US"), Currency.Type},
            {"Units / Contracts", each if _ = "-" then null else Number.FromText(_, "en-US"), type number},
            {"Realized Equity Change", each Number.FromText(_, "en-US"), Currency.Type},
            {"Realized Equity", each Number.FromText(Text.Remove(_, " "), "en-IN"), Currency.Type},
            {"Balance", each Number.FromText(Text.Remove(_, " "), "en-US"), Currency.Type},
            {"Position ID", Int64.From, Int64.Type},
            {"NWA", each Number.FromText(_, "en-US"), Currency.Type}
        }
    ),
    SplitDetails = Table.SplitColumn(
        FixValsTypes, "Details", Splitter.SplitTextByEachDelimiter({"/", " "}), 
        {"Details", "Currency", "Split details"}
    ),
    AddSplitValue = Table.AddColumn(
        SplitDetails,
        "Split value",
        each
            if [Split details] = null then
                null
            else
                [
                    split = Text.Split([Split details], ":"),
                    output = Number.FromText(split{0}) / Number.FromText(split{1})
                ][output],
        type number
    ),
    NewType = type table Type.ForRecord(
        Type.RecordFields(Type.TableRow(Value.Type(AddSplitValue)))
            & [
                Previous Balance = [Type = Currency.Type, Optional = false]
            ],
        false
    ),
    PreGroupSort = Table.Sort(AddSplitValue, {{"Position ID", Order.Ascending}, {"Date", Order.Ascending}}),
    Group = Table.Group(
        PreGroupSort,
        {"Position ID"},
        {
            "Fix",
            each [
                group = _,
                rowOrig = group{[Type = "Open Position"]},
                unitOrig = rowOrig[#"Units / Contracts"],
                splitProduct = List.Product(
                    List.Transform(
                        Table.SelectRows(group, each [Type] = "corp action: Split")[Split value], 
                        each 1 / _
                    )
                ),
                unitFixed = unitOrig * splitProduct,
                amountOrig = rowOrig[Amount],
                amountFix = Table.ReplaceValue(
                    group, each [Amount], amountOrig, Replacer.ReplaceValue, {"Amount"}
                ),
                rowCount = Table.RowCount(group),
                addPrevBalance = Table.Buffer(
                    Table.FromColumns(
                        Table.ToColumns(amountFix) & {{null} & List.RemoveLastN(group[Balance])}, 
                        NewType
                    )
                ),
                firstRecordFixed = Record.TransformFields(
                    Table.First(addPrevBalance), {{"Units / Contracts", each unitFixed ?? _}}
                ),
                newGen = List.Generate(
                    () => [i = 0, rows = {firstRecordFixed}, unit = unitFixed],
                    each [i] < rowCount,
                    each let currentUnit = [unit], currentRow = addPrevBalance{[i] + 1} in
                        [
                            i = [i] + 1,
                            rows = if currentRow[Type] = "corp action: Split" then
                                [
                                    splitSell = Record.TransformFields(
                                        currentRow,
                                        {
                                            {"Type", each "Split Sell"},
                                            {"Units / Contracts", each currentUnit}
                                        }
                                    ),
                                    splitBuy = Record.TransformFields(
                                        currentRow,
                                        {
                                            {"Type", each "Split Buy"}, 
                                            {"Units / Contracts", each unit}
                                        }
                                    ),
                                    splitOutput = {splitSell, splitBuy}
                                ][splitOutput]
                            else
                                {currentRow},
                            unit = [unit] * currentRow[Split value]
                        ],
                    each [rows]
                ),
                comboRows = List.Combine(newGen),
                output = Table.FromRecords(comboRows, NewType)
            ][output],
            NewType
        },
        GroupKind.Local
    ),
    CombineGroups = Table.Combine(Group[Fix]),
    AddPricePQ = Table.AddColumn(
        CombineGroups, "PricePQ", 
        each [Amount] / [#"Units / Contracts"], 
        type number
    )
in
    AddPricePQ