Best Way to Load Lots of Excel Files

[ddbaker]

Hi Everyone,

I'm a financial analyst with intermediate to advanced knowledge of Power Query and Power BI, but a complete novice when it comes to data architecture and engineering. We currently use Power BI and Excel for our Financial Planning tool, and it works great, but it's very slow.

We have an Excel template we use for our budgeting/forecasting, and we make a new file for each cost center and scenario. With 50 cost centers, 3 scenarios, and a budget and working forecast model, this comes out to be 300 Excel files. We currently load all 300 files into Power BI using the Folder Combine method, but the refresh times are painfully slow. This is a problem because we work in those files frequently and want to see our most up-to-date assumptions in our model quickly. For reference, in Power BI Desktop, refresh times can be in excess of 20 minutes.

I would imagine there must be a better way to handle the bulk import of these Excel files, but that's not my area of expertise. Are there other options through Azure or services like Snowflake that would speed up our model and still allow for the flexibility of our model to make changes to our forecasts as needed?

Thanks in advance for any help you can give!

[mahoneypat]

Your refresh time is likely driven more by the transformations you are doing vs. using Excel files as your source.  If you share your M code from the Advanced editor, someone here may be able to suggest a more optimal approach.

Alternatively, you could consider using a PowerApp with data stored in Dataverse for Teams to store your data instead of Excel.  Also, you could set up a Power Automate Flow to refresh your data as Excel files are added/modified on SharePoint, so that you take your refresh time offline (instead of you hitting Refresh and waiting 20 min).

Pat

[ddbaker]

Thanks for your response. Here are some samples of our code:

Budget Transformation

let
    Source = Folder.Files("M:\Strategic + Financial Planning and Analysis"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "04 Budget") and Text.Contains([Folder Path], "2 P&L") and not Text.Contains([Folder Path], "_old") and not Text.Contains([Folder Path], "template") and not Text.Contains([Folder Path], "draft")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Added Custom" = Table.AddColumn(#"Filtered Hidden Files1", "Custom", each Excel.Workbook([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Sheet")),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each not Text.Contains([Name], "Assumption") and Text.Contains([Name], "Budget") and not Text.Contains([Name], "Sanford")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows2",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39", "Column40", "Column41", "Column42", "Column43", "Column44", "Column45", "Column46", "Column47", "Column48", "Column49", "Column50", "Column51", "Column52", "Column53", "Column54", "Column55", "Column56", "Column57", "Column58", "Column59", "Column60", "Column61", "Column62", "Column63", "Column64", "Column65", "Column66", "Column67", "Column68", "Column69", "Column70", "Column71", "Column72", "Column73", "Column74", "Column75", "Column76", "Column77", "Column78", "Column79", "Column80", "Column81", "Column82", "Column83", "Column84", "Column85", "Column86", "Column87", "Column88", "Column89", "Column90", "Column91", "Column92", "Column93", "Column94", "Column95", "Column96", "Column97", "Column98"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Type", type text}, {"Period", Int64.Type}, {"Scenario-Name", type text}, {"Cost Center", type text}, {"Account", type text}, {"1/31/2019", Int64.Type}, {"2/28/2019", Int64.Type}, {"3/31/2019", Int64.Type}, {"4/30/2019", Int64.Type}, {"5/31/2019", Int64.Type}, {"6/30/2019", Int64.Type}, {"7/31/2019", Int64.Type}, {"8/31/2019", Int64.Type}, {"9/30/2019", Int64.Type}, {"10/31/2019", Int64.Type}, {"11/30/2019", Int64.Type}, {"12/31/2019", Int64.Type}, {"Column18", type any}, {"1/31/2020", Int64.Type}, {"2/29/2020", Int64.Type}, {"3/31/2020", Int64.Type}, {"4/30/2020", Int64.Type}, {"5/31/2020", Int64.Type}, {"6/30/2020", Int64.Type}, {"7/31/2020", Int64.Type}, {"8/31/2020", Int64.Type}, {"9/30/2020", Int64.Type}, {"10/31/2020", Int64.Type}, {"11/30/2020", Int64.Type}, {"12/31/2020", Int64.Type}, {"Column31", type any}, {"1/31/2021", Int64.Type}, {"2/28/2021", Int64.Type}, {"3/31/2021", Int64.Type}, {"4/30/2021", Int64.Type}, {"5/31/2021", Int64.Type}, {"6/30/2021", Int64.Type}, {"7/31/2021", Int64.Type}, {"8/31/2021", Int64.Type}, {"9/30/2021", Int64.Type}, {"10/31/2021", Int64.Type}, {"11/30/2021", Int64.Type}, {"12/31/2021", Int64.Type}, {"Column44", type any}, {"1/31/2022", Int64.Type}, {"2/28/2022", Int64.Type}, {"3/31/2022", Int64.Type}, {"4/30/2022", Int64.Type}, {"5/31/2022", Int64.Type}, {"6/30/2022", Int64.Type}, {"7/31/2022", Int64.Type}, {"8/31/2022", Int64.Type}, {"9/30/2022", Int64.Type}, {"10/31/2022", Int64.Type}, {"11/30/2022", Int64.Type}, {"12/31/2022", Int64.Type}, {"Column57", type any}, {"1/31/2023", Int64.Type}, {"2/28/2023", Int64.Type}, {"3/31/2023", Int64.Type}, {"4/30/2023", Int64.Type}, {"5/31/2023", Int64.Type}, {"6/30/2023", Int64.Type}, {"7/31/2023", Int64.Type}, {"8/31/2023", Int64.Type}, {"9/30/2023", Int64.Type}, {"10/31/2023", Int64.Type}, {"11/30/2023", Int64.Type}, {"12/31/2023", Int64.Type}, {"Column70", type any}, {"1/31/2024", Int64.Type}, {"2/29/2024", Int64.Type}, {"3/31/2024", Int64.Type}, {"4/30/2024", Int64.Type}, {"5/31/2024", Int64.Type}, {"6/30/2024", Int64.Type}, {"7/31/2024", Int64.Type}, {"8/31/2024", Int64.Type}, {"9/30/2024", Int64.Type}, {"10/31/2024", Int64.Type}, {"11/30/2024", Int64.Type}, {"12/31/2024", Int64.Type}, {"Column83", type any}, {"1/31/2025", Int64.Type}, {"2/28/2025", Int64.Type}, {"3/31/2025", Int64.Type}, {"4/30/2025", Int64.Type}, {"5/31/2025", Int64.Type}, {"6/30/2025", Int64.Type}, {"7/31/2025", Int64.Type}, {"8/31/2025", Int64.Type}, {"9/30/2025", Int64.Type}, {"10/31/2025", Int64.Type}, {"11/30/2025", Int64.Type}, {"12/31/2025", Int64.Type}, {"Column96", type any}, {"Column97", type any}, {"Column98", type any}}),
    #"Filtered Rows3" = Table.SelectRows(#"Changed Type", each ([Type] = "Budget")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows3", {"Type", "Period", "Scenario-Name", "Cost Center", "Account"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}, {"Value", "Amount"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}, {"Amount", Currency.Type}, {"Account", type text}}),
    #"Filtered Rows4" = Table.SelectRows(#"Changed Type1", each ([Amount] <> 0))
in
    #"Filtered Rows4"

PnL

let
    Source = Folder.Files("M:\Strategic + Financial Planning and Analysis"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "2 Income Statement") and not Text.Contains([Folder Path], "_old") and not Text.Contains([Folder Path], "Test")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Extracted Last Characters" = Table.TransformColumns(#"Expanded Table Column1", {{"Source.Name", each Text.End(_, 11), type text}}),
    #"Extracted First Characters" = Table.TransformColumns(#"Extracted Last Characters", {{"Source.Name", each Text.Start(_, 7), type text}}),
    #"Added Custom" = Table.AddColumn(#"Extracted First Characters", "Date", each [Source.Name]&"-01"),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date", type date}, {"$ Actuals", Currency.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Source.Name"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each not Text.Contains([Cost Center], "Total")),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([#"$ Actuals"] <> 0)),
    #"Filtered Rows3" = Table.SelectRows(#"Filtered Rows2", each not Text.Contains([Cost Center], "TOTAL")),
    #"Filtered Rows4" = Table.SelectRows(#"Filtered Rows3", each not Text.Contains([Account], "Total") and not Text.Contains([Account], "Gross") and not Text.Contains([Account], "Net")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows4",{{"$ Actuals", "Amount"}})
in
    #"Renamed Columns"

PnL Transform File

let
    Source = Csv.Document(Parameter1,[Delimiter=",", Columns=40, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39"}),
    #"Filled Up" = Table.FillUp(#"Replaced Value",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37", "Column38", "Column39"}),
    #"Kept First Rows" = Table.FirstN(#"Filled Up",1),
    #"Removed Top Rows" = Table.Skip(Source,1),
    #"Appended Query" = Table.Combine({#"Kept First Rows", #"Removed Top Rows"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Appended Query", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Ordinary Income/Expense"}, "Attribute", "Value"),
    #"Replaced Value1" = Table.ReplaceValue(#"Unpivoted Other Columns","Unclassified","0001 Operations InG Inc",Replacer.ReplaceText,{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each ([#"Ordinary Income/Expense"] <> "") and ([Value] <> "")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Ordinary Income/Expense", "Account"}, {"Attribute", "Cost Center"}, {"Value", "$ Actuals"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Period", each "2020"),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Type", each "Actual"),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Scenario-Name", each "Actual"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom3",{{"$ Actuals", Currency.Type}})
in
#"Changed Type2"

One thought that I've had is that we generally only change one underlying budget/forecast file at a time and then want to refresh our data set to see the updated projections. If there were away to just refresh one file, that would likely solve our problem. I haven't been able to find anything that says we can do that in Power BI though. Thanks for your help!

[mahoneypat]

Doing an incremental refresh on Excel files isn't natively available unfortunately.  Your queries don't look too bad.  There are a couple places you could consolidate steps to speed things up, or you could make a Dataflow so that the refresh happens offline and the refresh of your pbix file/report would be much faster.

It also may be because you have many files located at those filepaths.  Have you tried putting just the files you need in a subfolder and using that path directly in Folder.Files?  That should help, if you are not loading all those extra files (and then doing the filter steps).

Pat