Expression.Error of the record wasn't found - tried so many solutions!!

[zee1738]

Hi everyone, 

I'm currently trying to replace the empty values in a column conditional on two factors, if the cell is empty and if that cell's trade date equals the start date. if the cell meets these two conditions, then i want to perform a subtraction formula of essentially today NPV - (premium + upfront fee) (in the screenshot below). so far i've converted the necessary columns into record types and even created a debug column to ensure that the records contain the proper values. however, in the last step, i continuously get the result that "the field "trade_date" in the record wasn't found

i've provided screenshots of the error and parts of my code, ive exhausted all solutions so would greatly appreciate any help!! 

blocked out names for privacy 

the later half of my code ive copied below, removed names for privacy, so column names might not perfectly align but are not an issue in my actual code, NPV (Local) represents yesterday's value, matching the column screenshotted above that I want to edit : 

#"ReplaceNullValues" = Table.ReplaceValue(#"Reordered ColumnsFinal", null, 0, Replacer.ReplaceValue, {"premium_amount", "upfront_fee_amount", "NPV (Local)"}),

DefaultRecord = [#"NPV (Local)" = 0, #"premium_amount" = 0, #"upfront_fee_amount" = 0, #"trade_date" = Date.From(#"DateToday")],
ReplaceWithRecords = (columnValue as number , defaultValue as number) as record => if columnValue = null or columnValue = 0 then [Value = defaultValue] else [Value = columnValue],
DynamicDateRecord = (columnValue as nullable date, dynamicDate as date) as record => if columnValue = null then [Value = dynamicDate] else [Value = columnValue],

#"ChangedTypeX" = Table.TransformColumnTypes(#"ReplaceNullValues", {{"trade_date", type date}, {"TodayNPV (Local)", type number}}),

#"Replace Null" = Table.TransformColumns(#"ChangedTypeX", {
{"NPV (Local)", each ReplaceWithRecords(_, DefaultRecord[#"NPV (Local)"])},
{"premium_amount", each ReplaceWithRecords(_, DefaultRecord[#"premium_amount"])},
{"upfront_fee_amount", each ReplaceWithRecords(_, DefaultRecord[#"upfront_fee_amount"])},
{"trade_date", each DynamicDateRecord(_, Date.From(#"DateToday"))},
{"TodayNPV (Local)", each if _ = null then [Value = DefaultRecord[#"NPV (Local)"]] else [Value = _]}}),


#"DebugColumns" = Table.AddColumn(#"Replace Null", "DebugInfo", each [
PremiumValue = [#"premium_amount"][Value],
UpfrontValue = [#"upfront_fee_amount"][Value],
NPV = try [#"TodayNPV (Local)"][Value] otherwise "Error!",
TradeDateValue = [#"trade_date"][Value],
DateTodayValue = Date.From(#"DateToday")]),

#"NewPrevNPV" = Table.TransformColumns(#"DebugColumns", {{"NPV (Local)", each let premium = [#"premium_amount"][Value], upfront = [#"upfront_fee_amount"][Value], sumAmount = premium + upfront, detail = try [#"NPV (Local)"][Value] otherwise "Error", newNPV = if detail <> "Error" then detail - sumAmount else "Error", tradeDate = if [#"trade_date"][Value] is date then [#"trade_date"] else "trade_date_not_found" in if tradeDate = "trade_date_not_found" then "Record Error - not found" else if tradeDate = #"DateToday" then newNPV else _}})
in
#"NewPrevNPV"

[ronrsnfld]

Please provide sample data as text which can be copy/pasted, or provide a link to a sample data file along with the expected output from that data.

[zee1738]

Hi Karapurapu, 

Thank you so much for your help, but unfortunately I am still getting the same error, and when commenting out trade_date logic, the error moves to another column. Are there any other potential solutions? Or, if you know of how to complete this kind of logic in Excel rather than power query? Regardless thank you for providing a solution so early in the morning !

[v-karpurapud]

Hi @zee1738 

Thank you for the update. After applying the previous steps, the error may still persist because some of the columns involved such as trade_date, premium_amount, upfront_fee_amount, and NPV (Local) contain mixed data types. This means that while some cells are properly formatted as records with a [Value] field, others may still be null, primitive values, or records that are not structured as expected. As a result, when attempting to access the [Value] field, Power Query encounters cells that are not valid records, leading to the error message: "The field 'X' of the record wasn't found."

Try the updated M code:

NormalizeToRecord = (value) =>
    if value is record and Record.HasFields(value, "Value") then value
    else [Value = value],

#"NormalizedColumns" = Table.TransformColumns(#"ChangedTypeX", {
    {"NPV (Local)", NormalizeToRecord},
    {"premium_amount", NormalizeToRecord},
    {"upfront_fee_amount", NormalizeToRecord},
    {"TodayNPV (Local)", NormalizeToRecord},
    {"trade_date", NormalizeToRecord}
}),

#"NewPrevNPV" = Table.AddColumn(#"NormalizedColumns", "Updated NPV (Local)", each 
    let
        premium = [premium_amount][Value],
        upfront = [upfront_fee_amount][Value],
        todayNPV = [#"TodayNPV (Local)"][Value],
        tradeDate = [trade_date][Value],
        originalNPV = [#"NPV (Local)"][Value],

        sumAmount = premium + upfront,
        newNPV = if todayNPV <> null and sumAmount <> null then todayNPV - sumAmount else null,

        updatedNPV = if tradeDate = Date.From(#"DateToday") and (originalNPV = null or originalNPV = 0) then newNPV else originalNPV
    in
        updatedNPV
)

If this doesn’t fully meet your needs, could you kindly share a sample of your data and more detailed context? That would help us provide a more accurate solution.
 

Thank You.

[v-karpurapud]

Hi @zee1738 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

[zee1738 · Accepted solution]

Hi Karapurapud, 

Thank you again for all your help. I backtracked the code so that I didn't transform the data into records and instead work with as type Number and type Date. I provided my modified code below that ended up being a lot simpler : 

#"ChangedTypeX" = Table.TransformColumnTypes(#"ReplaceNullValues", {{"trade_date", type date}, {"Today_NPV (Local)", type number}, {"premium_amount", type number}, {"upfront_fee_amount", type number}}),


#"ModifiedRowsToday" = Table.FromRecords(List.Transform(Table.ToRecords(#"ChangedTypeX"), each Record.TransformFields(_, {
{"Prev_NPV (Local)", (val) => if val = null and [trade_date] = #"DateToday"
then ([#"Today_NPV (Local)"] - ([premium_amount] + [upfront_fee_amount])) else val}
}))),

#"ModifiedRowsCP" = Table.FromRecords(List.Transform(Table.ToRecords(#"ModifiedRowsToday"), each Record.TransformFields(_, {
{"Prev_CP_NPV (Local)", (val) => if val = null and [trade_date] = #"DateToday"
then ([#"CP_NPV (Local)"] - ([premium_amount] + [upfront_fee_amount])) else val}
}))),

#"ModifiedRowsMarket" = Table.FromRecords(List.Transform(Table.ToRecords(#"ModifiedRowsCP"), each Record.TransformFields(_, {
{"Prev_market_npv", (val) => if val = null and [trade_date] = #"DateToday"
then ([market_npv] - ([premium_amount] + [upfront_fee_amount])) else val}
}))),

#"Added Custom3" = Table.AddColumn(#"ModifiedRowsMarket", "Today_P&L", each [#"Today_NPV (Local)"] - [#"Prev_NPV (Local)"]),
#"CP_PNL" = Table.AddColumn(#"Added Custom3", "CP_P&L", each [#"CP_NPV (Local)"] - [#"Prev_CP_NPV (Local)"]),
#"Market_PNL" = Table.AddColumn(#"CP_PNL", "Market_P&L", each [#"market_npv"] - [#"Prev_market_npv"]),

[v-karpurapud]

Hi @zee1738 ,

Thank you for providing the update and the revised code. Could you please confirm if this has resolved the issue?

Thank you.

[v-karpurapud]

Hi @zee1738 

Thank you for contacting the Microsoft Fabric Community Forum.
 

The error message: "The field 'trade_date' of the record wasn't found"occurs because you are trying to access trade_date like a field directly from the row, but trade_date is now a record with a [Value] field inside. The same applies to other fields like premium_amount, upfront_fee_amount, and NPV (Local)  all of which have been converted to records (e.g., [Value = <actual_value>]) in previous transformation steps.

Try the below :

#"NewPrevNPV" = Table.AddColumn(#"DebugColumns", "Updated NPV (Local)", each 
    let
        premiumRec = [premium_amount],
        upfrontRec = [upfront_fee_amount],
        todayNPVRec = [#"TodayNPV (Local)"],
        tradeDateRec = [trade_date],
        originalNPVRec = [#"NPV (Local)"],

        premium = if Record.HasFields(premiumRec, "Value") then premiumRec[Value] else null,
        upfront = if Record.HasFields(upfrontRec, "Value") then upfrontRec[Value] else null,
        todayNPV = if Record.HasFields(todayNPVRec, "Value") then todayNPVRec[Value] else null,
        tradeDate = if Record.HasFields(tradeDateRec, "Value") then tradeDateRec[Value] else null,
        originalNPV = if Record.HasFields(originalNPVRec, "Value") then originalNPVRec[Value] else null,

        sumAmount = premium + upfront,
        newNPV = if todayNPV <> null and sumAmount <> null then todayNPV - sumAmount else null,

        updatedNPV = if tradeDate = Date.From(#"DateToday") and (originalNPV = null or originalNPV = 0) then newNPV else originalNPV
    in
        updatedNPV
)

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.