Hello,
I have the following query that was working great until I tried to remove some data. I have deleted data in one of the source tabs but when I refresh the query the data still exists. I have refreshed the query, edited the query, closed and reopened the file and cleared the data cache but the query won't refresh.
Any help would be much appreciated!
Thanks!
Shannon
let
FullFilePath = "M:Bus_ActivityService_Modernization_EVTCorp_IT_Financial ManagementForecast 2018-2019Fcast Monthly BCKUPSJan 21 Fcast Wrkbook_2018-19 - NEW.xlsx",
Source = Excel.Workbook(File.Contents(FullFilePath)),
#"Removed Columns" = Table.RemoveColumns(Source,{"Item", "Kind", "Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Name] <> "1IMT" and [Name] <> "2018 19 Div Frcst" and [Name] <> "2018 19 Project list" and [Name] <> "2018-19 EIE Budget" and [Name] <> "Actuals" and [Name] <> "Bud Rec" and [Name] <> "CAP" and [Name] <> "CAP spend Summary" and [Name] <> "Critical" and [Name] <> "EIP" and [Name] <> "Revenue" and [Name] <> "Rollup" and [Name] <> "salary" and [Name] <> "Service List" and [Name] <> "Summary")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "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"}, {"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"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Data", each ([Column2] <> null)),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", [PromoteAllScalars=true]),
#"Filtered Rows2" = Table.SelectRows(#"Promoted Headers", each ([DeptID] <> "Dept Id" and [DeptID] <> "DeptID" and [DeptID] <> "Director" and [DeptID] <> "FTE" and [DeptID] <> "Hidden rows below here " and [DeptID] <> "SA2" and [DeptID] <> "SA2 " and [DeptID] <> "SA2 " and [DeptID] <> "SA3" and [DeptID] <> "SA3 " and [DeptID] <> "SA3 " and [DeptID] <> "Senior Manager")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows2", "DeptID2", each Text.PadStart(Text.From([DeptID]),4,"0")),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"0010", "2", "DeptID", "DeptID2", "Program", "Budget Acct.", "Vendor Name ", "Contract #", "PO #", "Contact#(lf)Info.", "Comments ", "Start Date", "End Date", "Budget ", "APRIL", "MAY", "JUN", "JULY", "AUG", "SEPT", "OCT", "NOV", "DEC", "JAN", "FEB", "MAR", "Remaining and Accruals", "Total Forecast", "Surplus/(Deficit)", "Total Forecast_1", "Forecast Notes", "Budget _2", "Budget _3", "Budget _4", "Budget _5", "Budget _6", "Budget _7", "Oct talks with Leslie", "List of Services and Projects", "Percentage", "List of Services and Projects_8", "Percentage_9", "List of Services and Projects_10", "Percentage_11", "List of Services and Projects_12", "Percentage_13", "Critical", "Other #(lf)(inclds Projects)", "Impact #(lf)of delaying or cancelling line item in 'Other'", "Project - if associated to a delayed or canceled line item ", "Column50", "Column51", "Column52"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Oct talks with Leslie", "List of Services and Projects", "Percentage", "List of Services and Projects_8", "Percentage_9", "List of Services and Projects_10", "Percentage_11", "List of Services and Projects_12", "Percentage_13", "Critical", "Other #(lf)(inclds Projects)", "Impact #(lf)of delaying or cancelling line item in 'Other'", "Project - if associated to a delayed or canceled line item ", "Column50", "Column51", "Column52"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","7431E","7431",Replacer.ReplaceText,{"DeptID2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Vendor Name ", type text}})
in
#"Changed Type"
Hi Shannon,
Do you mean you have removed a table/worksheet in this file: "Jan 21 Fcast Wrkbook_2018-19 - NEW.xlsx", but the data is still being brought into the query? If so, are you able to share the file and query with me and tell me what data you still see that isn't in the source file? If it's confidential you can email me the file to website at myonlinetraininghub dot com.
Mynda
Hi Mynda,
I'm only deleting data within a worksheet, not the full worksheet, and the data is still in the query. It's almost like it's not 're-fetching' the data. This file is pretty messy, nothing is set to tables and there are 30+ worksheets that I'm trying to consolidate into one. Maybe I'll try a macro to convert to tables first...
Unfortunately I work for government and so can't share the file. I'll try recreating the query with the data formatted in tables and see if that helps.
Thanks!
Shannon
Hi Shannon,
I suspect the data is present elsewhere in your file as it's unlikely that it's not refreshing part of the query...just my guess based on past experience. You could test this theory by adding a row of dummy data back to the worksheet that you deleted the data from to see if it gets included?
Sorry I'm not much help without seeing the file, but I completely understand that you can't share it.
Mynda
I may have found a solution....
At first I was trying to run the query in the original workbook with all the data. I have now copied the M code into a blank workbook and not only does the query run much faster, but it appears to be refreshing correctly! Yay!
I'm thinking the original file was just too bulky to update the query properly. This was an ugly file that we already had to triage because it had too much formatting and was crashing all the time. Please people, do not add boarders to your massive spreadsheets! Just print gridlines!
Fingers crossed that this continues to work!
Glad you solved it. As painful as it is to start from scratch, often it's the quickest and best solution, so well done 🙂
