Hi everyone,
I'm encountering a "We cannot convert the value of type List to type Table" error in Power Query when trying to process a table of income data across multiple years and track a cumulative balance called "Net Operating Loss" (NOL)
Source Data ("Taxable_Income"):
My source data looks like this. It includes income (positive numbers) and loss (negative numbers) over several years for an entity:
Tax Year Entity Jurisdiction Amount
| 2020 | Entity 1 | IL | (500,000) |
| 2021 | Entity 1 | IL | (1,000,000) |
| 2022 | Entity 1 | IL | (3,500,000) |
| 2023 | Entity 1 | IL | 2,500,000 |
| 2024 | Entity 1 | IL | 600 |
Goal:
The goal is to process this data year by year. When the "Amount" is negative, it contributes to a running NOL balance. When the "Amount" is positive, it can be offset by this accumulated NOL from previous years. I need to track both the remaining NOL and how much of it was used each year.
Final Result (expected):
Here's the result I'm hoping to generate with this code
| Tax Year | Taxable Income | Year NOL Generated | CY NOL Generated/Utilized (negative indicates generation, and positive indicates Utilization) | Remaining NOL |
| 2020 | (500,000) | 2020 | (500,000) | (500,000) |
| 2021 | (1,000,000) | 2021 | (1,000,000) | (1,000,000) |
| 2022 | (3,500,000) | 2022 | (3,500,000) | (3,500,000) |
| 2023 | 2,500,000 | 2020 | 500,000 | 0 |
| 2023 | 2,500,000 | 2021 | 1,000,0000 | 0 |
| 2023 | 2,500,000 | 2022 | 1,000,000 | (2,500,000) |
| 2024 | 600 | 2022 | 600 | (2,499,400) |
Current Power Query Code:
Here's the Power Query code I've been working on. My code works if there's only 1 year of taxable income. When there are multiple years of positive income (2023 and 2024), I get the expression error "We cannot convert the value of type List to type Table". The issue occurs within the List.Accumulate function (in red) during the 2nd iteration (income in 2024 of 600). Does anyone have suggestions to resolve this error? Thanks!
let
Source = Excel.CurrentWorkbook(){[Name="Taxable_Income"]}[Content],
ChangedTypes = Table.TransformColumnTypes(Source,{{"Tax Year", Int64.Type}, {"Entity", type text}, {"Jurisdiction", type text}, {"Taxable Income", Int64.Type}}),
SortedRows = Table.Sort(ChangedTypes,{{"Tax Year", Order.Ascending}}),
AddedNOLGenerated = Table.AddColumn(SortedRows, "Year NOL Generated", each if [Taxable Income] < 0 then [Tax Year] else null, type number),
AddedCYNOLGenerated = Table.AddColumn(AddedNOLGenerated, "CY NOL Generated/Utilized", each [Taxable Income], type number),
FilteredNOLs = Table.SelectRows(AddedCYNOLGenerated, each Record.Field(_, "CY NOL Generated/Utilized") < 0),
AddedRemainingNOLColumn = Table.AddColumn(FilteredNOLs, "Remaining NOL", each null, type nullable number),
FilteredIncome = Table.SelectRows(AddedCYNOLGenerated, each Record.Field(_, "CY NOL Generated/Utilized") > 0),
// Function to calculate remaining NOLs
CalculateRemainingNOLs = (incomeTable as table, nolTable as table) =>
let
Result = List.Accumulate(
Table.ToRecords(incomeTable),
{nolTable, {}}, // Initial state: {current NOL table, list of utilized records}
(state, currentIncomeRecord) =>
let
currentIncome = currentIncomeRecord[#"CY NOL Generated/Utilized"],
currentTaxYear = currentIncomeRecord[Tax Year],
remainingNOLs = state{0}, // Current NOL table
accumulatedUtilizedRecords = state{1}, // Accumulated list of utilized records
Utilization = List.Accumulate(
Table.ToRecords(remainingNOLs),
{currentIncome, {}, remainingNOLs}, // Reset utilizedRecords for each income year
(innerState, currentNOLRecord) =>
let
currentIncomeToUtilize = innerState{0},
currentInnerUtilizedRecords = innerState{1},
currentNOLTable = innerState{2},
amountToUtilize = if currentIncomeToUtilize > 0 then List.Min({currentIncomeToUtilize, -currentNOLRecord[#"CY NOL Generated/Utilized"]}) else 0,
newIncomeToUtilize = currentIncomeToUtilize - amountToUtilize,
newRemainingNOL = currentNOLRecord[#"CY NOL Generated/Utilized"] + amountToUtilize,
newUtilizedRecord = if amountToUtilize > 0 then
Record.FromList(
{currentTaxYear, currentNOLRecord[Entity], currentNOLRecord[Jurisdiction], null, currentNOLRecord[#"Year NOL Generated"], amountToUtilize, newRemainingNOL},
{"Tax Year", "Entity", "Jurisdiction", "Taxable Income", "Year NOL Generated", "CY NOL Generated/Utilized", "Remaining NOL"}
)
else null,
newInnerUtilizedRecords = if newUtilizedRecord <> null then {newUtilizedRecord} & currentInnerUtilizedRecords else currentInnerUtilizedRecords,
updatedNOLTable = Table.TransformRows(currentNOLTable, each
if [#"Year NOL Generated"] = currentNOLRecord[#"Year NOL Generated"] then
Record.FromList(
{
_[#"Tax Year"],
_[Entity],
_[Jurisdiction],
_[#"Taxable Income"],
_[#"Year NOL Generated"],
newRemainingNOL,
_[#"Remaining NOL"] + amountToUtilize
},
Table.ColumnNames(currentNOLTable)
)
else
_
),
newNOLTable = Table.SelectRows(updatedNOLTable, each [#"CY NOL Generated/Utilized"] <> 0)
in
{newIncomeToUtilize, newInnerUtilizedRecords, newNOLTable}
),
finalRemainingNOLsForYear = Utilization{2},
utilizedRecordsForYear = Utilization{1}
in
{finalRemainingNOLsForYear, List.Combine({accumulatedUtilizedRecords, utilizedRecordsForYear})} // Explicitly combine lists
),
finalNOLTable = Result{0},
finalUtilizedRecords = Result{1}
in
finalUtilizedRecords,
// Apply the function
UtilizedNOLs = CalculateRemainingNOLs(FilteredIncome, FilteredNOLs),
// Convert the utilized list to a table
UtilizedNOLsTable = Table.FromRecords(UtilizedNOLs),
// Combine the utilized table with the original NOL table
CombinedTables = Table.Combine({FilteredNOLs, UtilizedNOLsTable}),
// Sort and remove unnecessary columns
SortedRows1 = Table.Sort(CombinedTables,{{"Tax Year", Order.Ascending}, {"Year NOL Generated", Order.Ascending}}),
RemovedColumns = Table.RemoveColumns(SortedRows1,{"Entity", "Jurisdiction"})
in
RemovedColumns
