Data is not loading in power bi due to complex calculation logic in power query

[YogeshWaran2010]

Hi all,

I am trying to calculate the depreciation of each machine every month. I have monthly data with the date as the file name, eg: '2024-11-01.CSV' for October data. I will add a custom column to mention the data's date. My issue is I have implemented a logic to calculate every month's depreciation based on the last available book value of the machine. It is working in power query. But I when importing the complete data to PowerBI, it is unable to compute such complex logic. I would like some support on how we can handle it. Thank you.

let
    // Step 1: Load the data
    Source = Excel.Workbook(File.Contents("D:\test\test data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
        {"Management Department Code", type text}, {"Management Department Name", type text}, {"Asset Type", type text}, 
        {"Parent-Child", type text}, {"Asset Number", type text}, {"Old Asset Number", type text}, {"Parent Asset Number", type text}, 
        {"Asset Name", type text}, {"Model", type text}, {"Acquisition Date", type date}, {"Acquisition Value", Int64.Type}, 
        {"Book Value as of Last Month-End", Int64.Type}, {"Monthly Depreciation Amount", Int64.Type}, {"Usage Status", type text}, {"Idle Asset", type text}, 
        {"Useful Life", Int64.Type}, {"Investment Management Number", type text}, {"Approval Number", type text}, {"Mold Number", type text}, 
        {"Local Code", type text}, {"Borrower", type text}, {"Vendor", type text}, {"Inventory Free Code", type text}, 
        {"Lease Contract Number", type text}, {"Transfer Source Management Department", type text}, {"Last Update Time", type datetime}
    }),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Usage Status] = "Active")),

    // Step 2: Add a Custom Starting Date Column
    #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Custom", each #date(2024, 10, 1)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom", {{"Custom", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Usage Status] = "Active")),

    // Step 3: Pre-Calculate Depreciation Cycles
    #"Added Depreciation Cycles" = Table.AddColumn(#"Filtered Rows", "DepreciationCycles", each
        Number.RoundDown(([Book Value as of Last Month-End] - 1) / [Monthly Depreciation Amount])
    ),
    #"Added End Date" = Table.AddColumn(#"Added Depreciation Cycles", "EndDate", each
        Date.AddMonths([Custom], [DepreciationCycles])
    ),

    // Step 4: Generate Monthly Rows for Each Equipment
    ExpandedRows = Table.AddColumn(#"Added End Date", "GeneratedRows", each
        List.Transform(
            List.Numbers(0, [DepreciationCycles] + 1),
            (n) => [ 
                Month = Date.AddMonths([Custom], n),
                Monthly Book Value as of Last Month-End = if n < [DepreciationCycles] then [Book Value as of Last Month-End] - (n * [Monthly Depreciation Amount]) else 1
            ]
        )
    ),

    // Step 5: Expand Generated Rows
    #"Expanded GeneratedRows" = Table.ExpandListColumn(ExpandedRows, "GeneratedRows"),
    #"Expanded Columns" = Table.ExpandRecordColumn(#"Expanded GeneratedRows", "GeneratedRows", {"Month", "Monthly Book Value as of Last Month-End"}),

    // Step 6: Final Cleanup
    FinalTable = Table.SelectColumns(#"Expanded Columns", {"Management Department Code", "Management Department Name", "Asset Type", "Asset Number", "Month", "Monthly Book Value as of Last Month-End"})
in
    FinalTable

EQ	Acquisition price	Book value at end of previous month	Monthly depreciation	Current month bookvalue	Date
NXT	14,200,000	591,665	147,917	443,748	2024/11/1
SPG	9,550,000	1,492,187	99,479	1,392,708	2024/11/1
NXT	14,200,000	443,748	147,917	295,831	2024/12/1
SPG	9,550,000	1,392,708	99,479	1,293,229	2024/12/1
NXT	14200000	295831	147917	147914	2025/1/1
SPG	9550000	1293229	99479	1193750	2025/1/1
NXT	14200000	147914	147917	1	2025/2/1
SPG	9550000	1193750	99479	1094271	2025/2/1
SPG	9550000	1094271	99479	994792	2025/3/1
SPG	9550000	994792	99479	895313	2025/4/1
SPG	9550000	895313	99479	795834	2025/5/1
SPG	9550000	795834	99479	696355	2025/6/1
SPG	9550000	696355	99479	596876	2025/7/1
SPG	9550000	596876	99479	497397	2025/8/1
SPG	9550000	497397	99479	397918	2025/9/1
SPG	9550000	397918	99479	298439	2025/10/1
SPG	9550000	298439	99479	198960	2025/11/1
SPG	9550000	198960	99479	99481	2025/12/1
SPG	9550000	99481	99479	2	2026/1/1
SPG	9550000	2	99479	1	2026/2/1

[AMeyersen]

Hi @YogeshWaran2010 ,

I am pretty sure the performance issue is caused by this step:

    // Step 4: Generate Monthly Rows for Each Equipment
    ExpandedRows = Table.AddColumn(#"Added End Date", "GeneratedRows", each
        List.Transform(
            List.Numbers(0, [DepreciationCycles] + 1),
            (n) => [ 
                Month = Date.AddMonths([Custom], n),
                Monthly Book Value as of Last Month-End = if n < [DepreciationCycles] then [Book Value as of Last Month-End] - (n * [Monthly Depreciation Amount]) else 1
            ]
        )
    ),

As I understand it, your approach involves duplicating each row n times and calculating the depreciation for each month until the book value reaches zero. However, this nested logic is too complex for Power Query to handle efficiently.

To better understand and address the performance issues, I suggest breaking this complex process into several simpler steps and measuring performance step by step:

1. Duplicate rows and add a [running number] for each piece of equipment.
2. Calculate the book value date using a formula like Date.AddMonths([start date], [running number]).
3. Calculate the depreciation amount, both as a monthly value and as a cumulative total, using a formula like MIN([running number] * [monthly depreciation], [book value]). This approach avoids referencing previous rows.
4. Calculate the [book value at the end of the month] as [book value] - [cumulative depreciation].

Power Query excels at performing simple operations on large datasets, but it struggles with complex logic applied at the row level.

By breaking the process into smaller, manageable steps, you can leverage Power Query's strengths and more easily pinpoint any performance bottlenecks.

[YogeshWaran2010]

@AMeyersen ,

Thank you. I used your suggestion to create a function which does not contain a loop function but a if else function which results in faster performance.

// Step 5: Pre-Calculate Depreciation Cycles
#"Added Depreciation Cycles" = 
    Table.AddColumn(
        #"Changed Type1", 
        "DepreciationCycles", 
        each Number.RoundDown(([Book value at the end of previous month] - 1) / [Monthly depreciation amount])
    ), 

// Step 6: Generate Period Indices for Monthly Rows
#"Generated Period Indices" = 
    Table.AddColumn(
        #"Added Depreciation Cycles", 
        "PeriodIndices", 
        each List.Numbers(0, [DepreciationCycles] + 2)
    ),
    
#"Expanded Period Indices" = 
    Table.ExpandListColumn(
        #"Generated Period Indices", 
        "PeriodIndices"
    ),

// Step 7: Calculate Monthly Dates and Depreciation
#"Calculated Monthly Dates" = 
    Table.AddColumn(
        #"Expanded Period Indices", 
        "Month", 
        each Date.AddMonths([StartDate], [PeriodIndices])
    ),

#"Calculated Depreciation Amounts" = 
    Table.AddColumn(
        #"Calculated Monthly Dates", 
        "Cumulative Depreciation", 
        each if ([PeriodIndices] * [Monthly Depreciation Amount]) > [Book Value at End of Previous Month] 
            then ([Book Value at End of Previous Month] - 1) 
            else ([PeriodIndices] * [Monthly Depreciation Amount])
    ),

#"Calculated Monthly Book Value" = 
    Table.AddColumn(
        #"Calculated Depreciation Amounts", 
        "Book value at end of previous month_monthly", 
        each [Book value at end of previous month] - [Cumulative Depreciation]
    )

[YogeshWaran2010]

Hi @v-yohua-msft,
I have looked into your suggestion. But the issue I am facing now is the dataset itself is not loading into Power BI. I have the transformation and other steps but when I click 'Close and apply' the Excel dataset loads for 48KBs and keeps on loading at 48KBs. So I asked for a suggestion to improve my Power Query steps for performance. Thank you.

[v-yohua-msft · Accepted solution]

Hi, @YogeshWaran2010 

If you have a large dataset, consider using incremental refresh in Power BI. This improves performance by refreshing only new or changed data, rather than the entire dataset. You can check the following links:

Configure incremental refresh for Power BI semantic models - Power BI | Microsoft Learn

Understand and optimize dataflows refresh - Power BI | Microsoft Learn

At the same time, you can also consider moving some of your calculations from Power Query to DAX (Data Analysis Expressions) in Power BI. DAX is optimized for performance and may be better suited for handling complex calculations.

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.