1 ACCEPTED SOLUTION
I think this should work with the updated sample and requirements. Quick summary of changes:
-
Updated Position ID transformation in FixValTypes to handle -'s as blanks
-
Within Group, defined noOpen check and used in output step to just return grouped rows as is if no Open Positions are found in group
-
Within Group, updated amountFixed step to only apply Open Position Amount to Types "Position closed" and "corp action: Split"
-
Within Group, at newGen step (List.Generate):
-
Updated rows iteration to apply current Units to dividends (also, reorganized conditional for clarity)
-
Updated unit iteration to have the multiplied split value default to 1 (so, keep unit as is) in case split value is null (otherwise, dividend or any other non-split transactions with null split value would null out the unit getting passed forward)
-
let
Source = Original_Updated,
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", each if _ = "-" then null else 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 = _,
noOpen = not List.Contains( group[Type], "Open Position"),
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],
each
if List.Contains( {"Position closed", "corp action: Split"}, [Type])
then amountOrig
else [Amount],
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 =
[
splitSell = Record.TransformFields(
currentRow,
{
{"Type", each "Split Sell"},
{"Units / Contracts", each currentUnit}
}
),
splitBuy = Record.TransformFields(
currentRow,
{
{"Type", each "Split Buy"},
{"Units / Contracts", each unit}
}
),
dividend = Record.TransformFields(
currentRow,
{{"Units / Contracts", each currentUnit}}
),
noChange = currentRow,
output =
if currentRow[Type] = "corp action: Split"
then {splitSell, splitBuy}
else if currentRow[Type] = "Dividend"
then {dividend}
else {noChange}
][output],
unit = [unit] * (currentRow[Split value] ?? 1)
],
each [rows]
),
comboRows = List.Combine(newGen),
output = if noOpen then group else 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
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
So, to confirm the updated reqs:
1) Any Position ID groups (including where Position ID is null) that do not have an Open Position row included can just be left as is?
Example: no transformations are needed on the below outlined rows (1-2 & 6) as they have no associated Open Position?
2) For Position ID groups with an Open Position row:
a) ALL other rows should use the Open Position Amount?
Example: for the below 906... group, the Amount for ALL rows should match Open Position's, i.e. 50?
b) I'm guessing the Units should be based on most recent previous Units, whether that is Open Position or a split buy?
Example: If the timestamp of the Edit Stop Loss below put it after the first 1:10 split, then the associated Units should match the updated Units (which would be on the Split Buy row that gets added)?
c) Sort of covered already indirectly, but want to directly check, any row with a timestamp that is earlier than the Open Position row should also get assigned the Open Position row's (recalculated, if group has splits) Units? Same with Amount? Or should it stay as is?
Example: What should happen to Units and Amount of the Overnight fee row in the 906... group?
I think this should work with the updated sample and requirements. Quick summary of changes:
-
Updated Position ID transformation in FixValTypes to handle -'s as blanks
-
Within Group, defined noOpen check and used in output step to just return grouped rows as is if no Open Positions are found in group
-
Within Group, updated amountFixed step to only apply Open Position Amount to Types "Position closed" and "corp action: Split"
-
Within Group, at newGen step (List.Generate):
-
Updated rows iteration to apply current Units to dividends (also, reorganized conditional for clarity)
-
Updated unit iteration to have the multiplied split value default to 1 (so, keep unit as is) in case split value is null (otherwise, dividend or any other non-split transactions with null split value would null out the unit getting passed forward)
-
let
Source = Original_Updated,
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", each if _ = "-" then null else 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 = _,
noOpen = not List.Contains( group[Type], "Open Position"),
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],
each
if List.Contains( {"Position closed", "corp action: Split"}, [Type])
then amountOrig
else [Amount],
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 =
[
splitSell = Record.TransformFields(
currentRow,
{
{"Type", each "Split Sell"},
{"Units / Contracts", each currentUnit}
}
),
splitBuy = Record.TransformFields(
currentRow,
{
{"Type", each "Split Buy"},
{"Units / Contracts", each unit}
}
),
dividend = Record.TransformFields(
currentRow,
{{"Units / Contracts", each currentUnit}}
),
noChange = currentRow,
output =
if currentRow[Type] = "corp action: Split"
then {splitSell, splitBuy}
else if currentRow[Type] = "Dividend"
then {dividend}
else {noChange}
][output],
unit = [unit] * (currentRow[Split value] ?? 1)
],
each [rows]
),
comboRows = List.Combine(newGen),
output = if noOpen then group else 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
Helpful resources
Fabric Community Update - July 2025
Find out what's new and trending in the Fabric community.
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
