Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Refreshing Error Due to Insufficient Memory

Hi All,

 

I have a PowerBI report that is pulling in data from an excel spreadhseet that has around 5000 rows with 15 columns. Certainly I had to perform a lot of transformations and calculation to generate the report that was asked for. It was loading properly until the recent August update, but I am not sure if this is the issue as I have made some more changes. 

Note: I am referring to a table in various queries as source table  to perform other tasks, process it in a different way and I can see it is taking upto 25GB of RAM in the task Manager, but still failing. Please let me know your suggestions on this. Thank everyone in advance.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Quite a lot going on in these queries.  I think you will need to spend some time looking for optimisations.  Some code rows that stood out to me.

 

In RAW:

#"Add Col Tables promote headers" = Table.AddColumn(#"Removed Columns1", "GetExcelPromoteHeaders", each Table.PromoteHeaders([Data])),

That looks like a piece of code that has potential to blow out.  I'm unsure what the exact reason to do this row by row is?

In RAW:

#"Invoked Custom Function" = Table.AddColumn(#"Remove old no header tables", "AddColSheetName", each AddColSheetName([GetExcelPromoteHeaders], [Item])),

Check that this function isn't causing you issues.  Its hard to say from the outside.

In the other query:

#"Added Custom" = Table.AddColumn(Source, "Custom", each #"Submit Date"),

This stood out to me as being a strange item.  You are attempting to get a single date, but seeming to be joining a whole table on each row?

 

View solution in original post

8 REPLIES 8
v-yuezhe-msft
Employee
Employee

@Anonymous,

Firstly, do you use 32 bit or 64 bit Power BI Desktop? Switch to use 64 bit Power BI Desktop if it is possible.

Secondly, turn off the following options in your PBIX file.
1.PNG

Thirdly, do you need to load all the tables to Power BI Data view? You can consider to disable "Enable load" option for some bridge tables.

1.PNG

At last, how much RAM on  your machine? Do you run other applications at the same time?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you for the comments. 

 

1. I am using a 64 bit version of PowerBI

2. I have disabled the mentioned options already.

3.  I have a virtual maching with 25GB of RAM and using it only for this report.

Anonymous
Not applicable

Just a double check on your Excel file.  You have stated its only 5000 rows.  When you open it in Excel, if you were to grab the scroll bars and manually drag it to the bottom, does it stop at the 5000 rows, or does it take you down well into the blank spaces?

 

A common issue in excel is to track the "Used Range".  Conceptually your file might be 5000 rows, but Excel might have had a reason to believe your file is much longer and larger.

 

The solution to this problem, if its occuring, is to scroll to the last row and selected all of the back rows (row 5001 until the bottom) and then select "Delete Rows".  Saving the workbook after doing this should shrink the size of the workbook backdown.

 

 

The Same problem can occur with the columns.

Anonymous
Not applicable

Thanks for the reply, this was something I wasn't aware and I tried following your errors but still Memory issue exist. A bit of back ground, I have an excel spreadsheet and I am appending two queries(spreadsheet) for a transformation this is where the load is high and is failing.

Anonymous
Not applicable

Nothing specifically about your description indicates what the cause is likely to be.

 

For the purposes of testing, if you create a simple version of your import with the only steps being the appending of the 2 tables into one, do you get the same result?  I wonder if there is a step inside your import query that is causing the problem that hasn't stood out to you.

 

Can you post the Power Query code? We might have an idea when we see how its written.

Anonymous
Not applicable

Sure. Please find the powerQuery below.

 

let
Source = #"raw",
#"Added Custom" = Table.AddColumn(Source, "Custom", each #"Submit Date"),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Date"}, {"Custom.Date"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Date", "Current Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Current Date", type date}}),
#"Past Type" = Table.AddColumn(#"Changed Type", "Past Type", each "Inactive"),
#"Added Custom2" = Table.AddColumn(#"Past Type", "Future Type", each if [Billing Month] <> [Current Date] then "Inactive" else "Annuity Existing" ),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Current Date"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Future Type", type text}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type2", {"Service Delivery", "Contract OPS - Field Services", "Contract MS - Managed Services", "Contract PS - Prof. Services", "Contract Cloud – Managed Backup", "Contract OPS - Service Desk", "Contract MS - Hosting", "Contract PS - Unified Comms", "Prepaid hours balance sheet" }, "Attribute", "Value"),
#"Renamed Columns2" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Attribute", "Business Unit"}, {"Value", "Revenue"}}),
#"Unpivoted Only Selected Columns1" = Table.Unpivot(#"Renamed Columns2", { "Current Type", "Past Type", "Future Type"}, "Attribute", "Value"),
#"Renamed Columns3" = Table.RenameColumns(#"Unpivoted Only Selected Columns1",{{"Attribute", "Relative Month Status"}, {"Value", "Revenue Type"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns3",null,1,Replacer.ReplaceValue,{"Billing Period"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",0,1,Replacer.ReplaceValue,{"Billing Period"}),
#"Added Custom1" = Table.AddColumn(#"Replaced Value1", "Monthly Revenue", each if [Revenue Type] <> "Inactive" then [Revenue] / [Billing Period] else null),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Monthly Revenue", Currency.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Monthly Revenue", "Monthly Revenue Raw"}}),
#"Calculated Start of Month" = Table.TransformColumns(#"Renamed Columns1",{{"Billing Month", Date.StartOfMonth, type date}}),
#"Removed Columns1" = Table.RemoveColumns(#"Calculated Start of Month",{"Revenue"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns1",{{"Billing Month", "Submission Month"}}),
#"Appended Query" = Table.Combine({#"Renamed Columns4", #"Account Contract Overview"}),
#"Replaced Value2" = Table.ReplaceValue(#"Appended Query","","ERROR - no Rev Type",Replacer.ReplaceValue,{"Revenue Type"}),
#"Sorted Rows" = Table.Sort(#"Replaced Value2",{{"Revenue Type", Order.Descending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Revenue Type] <> "Inactive") and ([Relative Month Status] <> "Past Type"))
in
#"Filtered Rows"

 

 

 

 

 

 

 

 

 

 

The source for this is another query (#raw)which is as follows.

 

let
Source = SharePoint.Files("https://******", [ApiVersion = 15]),
#"Revenue Forecast register xlsx_https://*********/" = Source{[Name="Revenue Forecast register.xlsx",#"Folder Path"="https://******/Shared Documents/General/"]}[Content],
#"Imported Excel" = Excel.Workbook(#"Revenue Forecast register xlsx_https://******/Shared Documents/General/"),
#"Filtered Rows" = Table.SelectRows(#"Imported Excel", each Text.StartsWith([Name], "Jan") or Text.StartsWith([Name], "Feb") or Text.StartsWith([Name], "Mar") or Text.StartsWith([Name], "Apr") or Text.StartsWith([Name], "May") or Text.StartsWith([Name], "Jun") or Text.StartsWith([Name], "Jul") or Text.StartsWith([Name], "Aug") or Text.StartsWith([Name], "Sep") or Text.StartsWith([Name], "Oct") or Text.StartsWith([Name], "Nov") or Text.StartsWith([Name], "Dec")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name", "Kind", "Hidden"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Data", "Data - Copy"),
#"Removed Columns1" = Table.RemoveColumns(#"Duplicated Column",{"Data - Copy"}),
#"Add Col Tables promote headers" = Table.AddColumn(#"Removed Columns1", "GetExcelPromoteHeaders", each Table.PromoteHeaders([Data])),
#"Remove old no header tables" = Table.RemoveColumns(#"Add Col Tables promote headers",{"Data"}),
#"Invoked Custom Function" = Table.AddColumn(#"Remove old no header tables", "AddColSheetName", each AddColSheetName([GetExcelPromoteHeaders], [Item])),
#"Removed Columns2" = Table.RemoveColumns(#"Invoked Custom Function",{"GetExcelPromoteHeaders"}),
#"Combined data" = Table.Combine(#"Removed Columns2"[AddColSheetName]),
#"Renamed Columns" = Table.RenameColumns(#"Combined data",{{"", "Billing Period"}, {"Number", "TrackIT Ref"}}),
#"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns",{"Column16", "Column17", "5", "Column1", "Revenue - PST", "Revenue - Janet Dymond"}),
#"Replace 1 Month(s)" = Table.ReplaceValue(#"Removed Columns3","1 Month(s)","1",Replacer.ReplaceText,{"Billing Period"}),
#"Replace 3 Month(s)" = Table.ReplaceValue(#"Replace 1 Month(s)","3 Month(s)","3",Replacer.ReplaceText,{"Billing Period"}),
#"Replace 1 Year(s)" = Table.ReplaceValue(#"Replace 3 Month(s)","1 Year(s)","12",Replacer.ReplaceText,{"Billing Period"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replace 1 Year(s)",{{"Contract SDM", Currency.Type}}),
#"Changed Currency Cols Type" = Table.TransformColumnTypes(#"Changed Type",{{"Billing Period", Int64.Type}, {"Contract OPS - Field Services", Currency.Type}, {"Contract MS - Managed Services", Currency.Type}, {"Contract PS - Prof. Services", Currency.Type}, {"(Contract Cloud – Managed Backup)", Currency.Type}, {"Contract OPS - Service Desk", Currency.Type}, {"Contract MS - Hosting", Currency.Type}, {"Contract PS - Unified Comms", Currency.Type}, {"Prepaid hours balance sheet", Currency.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Currency Cols Type",{{"(Contract Cloud – Managed Backup)", "Contract Cloud – Managed Backup"}, {"SourceSheet", "Billing Month"}, {"Contract Name", "Description"}}),
#"Changed Billing Month Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Billing Month", type date}}),
#"Remove billing month errors (0 days etc)" = Table.RemoveRowsWithErrors(#"Changed Billing Month Type", {"Billing Period"}),
#"Added Rev Type Col" = Table.AddColumn(#"Remove billing month errors (0 days etc)", "Current Type", each "Annuity Existing"),
#"Added Custom" = Table.AddColumn(#"Added Rev Type Col", "Source", each "Trackit"),
#"Renamed Columns2" = Table.RenameColumns(#"Added Custom",{{"Contract SDM", "Service Delivery"}})
in
#"Renamed Columns2"

Anonymous
Not applicable

Quite a lot going on in these queries.  I think you will need to spend some time looking for optimisations.  Some code rows that stood out to me.

 

In RAW:

#"Add Col Tables promote headers" = Table.AddColumn(#"Removed Columns1", "GetExcelPromoteHeaders", each Table.PromoteHeaders([Data])),

That looks like a piece of code that has potential to blow out.  I'm unsure what the exact reason to do this row by row is?

In RAW:

#"Invoked Custom Function" = Table.AddColumn(#"Remove old no header tables", "AddColSheetName", each AddColSheetName([GetExcelPromoteHeaders], [Item])),

Check that this function isn't causing you issues.  Its hard to say from the outside.

In the other query:

#"Added Custom" = Table.AddColumn(Source, "Custom", each #"Submit Date"),

This stood out to me as being a strange item.  You are attempting to get a single date, but seeming to be joining a whole table on each row?

 

Anonymous
Not applicable

I started testing seperately with the excel and htere seems to data format change that was bloating the file and is much faster after I  removed some steps in the power query as suggested thank 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.