Server Error After Merge Query - Unable to Apply Changes

[JaclynPugh2022]

Hi,

Can someone direct me to how I would fix these errors when trying to apply query changes? 

My Power BI Desktop database is 34,000 KB in size.

PC: Windows 10 Pro

64-bit operating system, x64-based processor

16.0 GB (15.8 GB usable)

How much memory do I need to add?  Also, the 'received pivot' query is NOT looking for a "Date" field, but an error shows I'm getting indicates that one is missing - how can I find where this date field is missing from?  I have included the advanced query details below.

Error (happens after closing query editor and applying changes to query):

Failed to save modifications to the server. Error returned: 'There's not enough memory to complete this operation. Please try again later when there may be more memory available. Column 'Date' in table 'received pivot' cannot be found or may not be used in this expression. Column 'Date' in table 'received pivot' cannot be found or may not be used in this expression. '.

Advanced Editor:

let
Source = Excel.Workbook(File.Contents("C:\Users\pughj\Documents\Start\Received_Pivot.xlsx"), null, true),
#"received pivot_Sheet" = Source{[Item="received pivot",Kind="Sheet"]}[Data],
#"Promoted Headers1" = Table.PromoteHeaders(#"received pivot_Sheet", [PromoteAllScalars=true]),
#"Changed Type6" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Product Number", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type6", "If Date", each if [Year] = "2021" then 2021 else if [Year] = "2022" then 2022 else if [Year] = "2023" then 2023 else null),
#"Merged Queries" = Table.NestedJoin(#"Added Conditional Column", {"Product Number"}, #"New & Std Cost", {"Item"}, "New & Std Cost", JoinKind.LeftOuter),
#"Duplicated Column5" = Table.DuplicateColumn(#"Merged Queries", "Receipt Date", "Receipt Date - Copy"),
#"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column5",{{"Receipt Date - Copy", type date}}),
#"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"Receipt Date - Copy", each Date.MonthName(_), type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Month Name",{"Receipt Date - Copy"}),
#"Expanded New & Std Cost" = Table.ExpandTableColumn(#"Removed Columns", "New & Std Cost", {"2022 Std Price", "2021 Std Price"}, {"2022 Std Price", "2021 Std Price"}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Expanded New & Std Cost", "Vendor", "Vendor - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column1", "Vendor - Copy", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Vendor - Copy.1", "Vendor - Copy.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Vendor - Copy.1", type text}, {"Vendor - Copy.2", type text}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"Vendor - Copy.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Vendor - Copy.1", "Vendor Name"}}),
#"Duplicated Column2" = Table.DuplicateColumn(#"Renamed Columns", "Warehouse", "Warehouse - Copy"),
#"Split Column by Position" = Table.SplitColumn(#"Duplicated Column2", "Warehouse - Copy", Splitter.SplitTextByPositions({0, 3}, false), {"Warehouse - Copy.1", "Warehouse - Copy.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Warehouse - Copy.1", Int64.Type}, {"Warehouse - Copy.2", type text}}),
#"Removed Columns3" = Table.RemoveColumns(#"Changed Type2",{"Warehouse - Copy.2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns3",{{"Warehouse - Copy.1", "WHS Code"}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Renamed Columns1",{{"Vendor Name", Text.Proper, type text}}),
#"Duplicated Column3" = Table.DuplicateColumn(#"Capitalized Each Word", "Product Number", "Product Number - Copy"),
#"Split Column by Position1" = Table.SplitColumn(#"Duplicated Column3", "Product Number - Copy", Splitter.SplitTextByPositions({0, 3}, false), {"Product Number - Copy.1", "Product Number - Copy.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Product Number - Copy.1", Int64.Type}, {"Product Number - Copy.2", type text}}),
#"Removed Columns4" = Table.RemoveColumns(#"Changed Type3",{"Product Number - Copy.2"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns4",{{"Product Number - Copy.1", "Left 3 of Item Num"}}),
#"Duplicated Column4" = Table.DuplicateColumn(#"Renamed Columns2", "PO Text", "PO Text - Copy"),
#"Trimmed Text" = Table.TransformColumns(#"Duplicated Column4",{{"PO Text - Copy", Text.Trim, type text}}),
#"Uppercased Text" = Table.TransformColumns(#"Trimmed Text",{{"PO Text - Copy", Text.Upper, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Uppercased Text","@","",Replacer.ReplaceText,{"PO Text - Copy"}),
#"Split Column by Position2" = Table.SplitColumn(#"Replaced Value", "PO Text - Copy", Splitter.SplitTextByPositions({0, 28}, false), {"PO Text - Copy.1", "PO Text - Copy.2"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Position2",{{"PO Text - Copy.1", type text}, {"PO Text - Copy.2", type text}}),
#"Removed Columns5" = Table.RemoveColumns(#"Changed Type4",{"PO Text - Copy.2"}),
#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns5",{{"PO Text - Copy.1", "Item Trimmed"}}),
#"Changed Type5" = Table.TransformColumnTypes(#"Renamed Columns3",{{"Vendor Number", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type5",{"Left 3 of Item Num"}),
#"Product Num Copy for Left 3" = Table.DuplicateColumn(#"Removed Columns1", "Product Number", "Product Number - Copy"),
#"Uppercased Text1" = Table.TransformColumns(#"Product Num Copy for Left 3",{{"Product Number - Copy", Text.Upper, type text}}),
#"Trimmed Text1" = Table.TransformColumns(#"Uppercased Text1",{{"Product Number - Copy", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text1",{{"Product Number - Copy", Text.Clean, type text}}),
#"Split Column by Position3" = Table.SplitColumn(#"Cleaned Text", "Product Number - Copy", Splitter.SplitTextByPositions({0, 3}, false), {"Product Number - Copy.1", "Product Number - Copy.2"}),
#"Changed Type7" = Table.TransformColumnTypes(#"Split Column by Position3",{{"Product Number - Copy.1", Int64.Type}, {"Product Number - Copy.2", type text}}),
#"Removed Columns6" = Table.RemoveColumns(#"Changed Type7",{"Product Number - Copy.2"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns6",{{"Product Number - Copy.1", "Left 3 of Item Num"}}),
#"Left 3 as text" = Table.TransformColumnTypes(#"Renamed Columns4",{{"Left 3 of Item Num", type text}}),
#"Cleaned Text1" = Table.TransformColumns(#"Left 3 as text",{{"Left 3 of Item Num", Text.Clean, type text}}),
#"Trimmed Text2" = Table.TransformColumns(#"Cleaned Text1",{{"Left 3 of Item Num", Text.Trim, type text}}),
#"Changed Type8" = Table.TransformColumnTypes(#"Trimmed Text2",{{"Receipt Date", type date}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type8", "Receipt Date", "Receipt Date - Copy"),
#"Calculated Quarter" = Table.TransformColumns(#"Duplicated Column",{{"Receipt Date - Copy", Date.QuarterOfYear, Int64.Type}}),
#"Renamed Columns5" = Table.RenameColumns(#"Calculated Quarter",{{"Receipt Date - Copy", "Quarter"}}),
#"Changed Type9" = Table.TransformColumnTypes(#"Renamed Columns5",{{"Year", Int64.Type}}),
#"Removed Columns7" = Table.RemoveColumns(#"Changed Type9",{"Line Filled On Time", "Line On Time Rate %"}),
#"Changed Type10" = Table.TransformColumnTypes(#"Removed Columns7",{{"If Date", Int64.Type}, {"PO Received Cost", type number}, {"PO Received Quantity", Int64.Type}, {"Avg PO Received Price", type number}}),
#"Merged Queries1" = Table.NestedJoin(#"Changed Type10", {"Product Number"}, PPO, {"Item number"}, "PPO", JoinKind.LeftOuter),
#"Expanded PPO" = Table.ExpandTableColumn(#"Merged Queries1", "PPO", {"PPO Delivery Date", "PPO Qty"}, {"PPO Delivery Date", "PPO Qty"}),
#"Merged Queries2" = Table.NestedJoin(#"Expanded PPO", {"Product Number"}, #"Open Conf", {"Product Number"}, "Open Conf", JoinKind.LeftOuter),
#"Expanded Open Conf" = Table.ExpandTableColumn(#"Merged Queries2", "Open Conf", {"Open Conf Delivery Date", "Open Conf Quantity"}, {"Open Conf Delivery Date", "Open Conf Quantity"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Open Conf", each true),
#"Merged Queries3" = Table.NestedJoin(#"Filtered Rows", {"Product Number"}, TA, {"Item Number"}, "TA", JoinKind.LeftOuter),
#"Expanded TA" = Table.ExpandTableColumn(#"Merged Queries3", "TA", {"TA Purchase Pool", "TA Valid From", "TA Price"}, {"TA Purchase Pool", "TA Valid From", "TA Price"}),
#"Removed Columns8" = Table.RemoveColumns(#"Expanded TA",{"Last Update Purchasing"})
in
#"Removed Columns8"

[v-xiaoyan-msft]

Hi @JaclynPugh2022 ,

If you are using Power BI Desktop, try increasing the Maximum allowed to 16000 under File >Options and Settings > Options > Global > Data Load> Data Cache Management Options.

You may  also turn off Background data in File > Options > Current File > Data Load.
Or in Query Editor > Right Click on Table> Un-check Enable Load.

Best Regards,
Community Support Team _ Caitlyn

[JaclynPugh2022]

Hi Caitlyn,

I updated the max data cash to 16000 as suggested, and this did not resolve the issue.  I went ahead and turned off background data as well, and no resolution.  Do you have any other suggestions by chance?  

Thank you

Error:

received pivot

Failed to save modifications to the server. Error returned: There's not enough memory to complete this operation. Please try again later when there may be more memory available. Column 'Date' in table 'received pivot' cannot be found or may not be used in this expression. Column 'Date' in table 'received pivot' cannot be found or may not be used in this expression.

[JaclynPugh2022]

Latest Error:  ***How much RAM do I need to get this off the ground?  I have 16GB at the moment.

I have made the following modifications to options/settings, which does not help with the memory issue: