Forum Discussion
Power Query Data is updating in PQ Editor, but not in Excel Table
I have a Power Query through Excel. I haven't change anything to this file, except the same data source was updated with new data.
I cleared the cache, and refresh preview.
The new data is showing in the Power Query Editor, and in the Data Model. But the new data is not showing on the Table in Excel. How to I get the Table in Excel to update please?
Here is the Power Query Code:
let
Source = Csv.Document(File.Contents("C:\Users\cday\OneDrive - udfinc.com\M6 Scorecard\Data Pulls\Weekly Data.csv"),[Delimiter=",", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Removed Top Rows" = Table.Skip(Source,15),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"WEEK_NAME", type text}, {"ITM_SCN_DEPT_DESC", type text}, {"ITM_SCN_CDTY_DESC", type text}, {"ITM_SCN_SUB_CDTY_DESC", type text}, {"MFR_CD", Int64.Type}, {"MFR_DESC", type text}, {"RPT_SHORT_DESC", type text}, {"ITEM_DESCRIPTION", type text}, {"UPC", Int64.Type}, {"SCANNED_RETAIL_DOLLARS", type number}, {"SCANNED_MOVEMENT", Int64.Type}, {"SCANNED_LBS", Int64.Type}, {"GROSS_MARGIN_DOLLARS", type number}, {"AVG_RETAIL", type number}, {"ITM_SCN_PRC_GRP", Int64.Type}, {"ITM_SCN_PRC_GRP_DESC", type text}}),
#"Trimmed Text" = Table.TransformColumns(Table.TransformColumnTypes(#"Changed Type", {{"UPC", type text}}, "en-US"),{{"UPC", Text.Trim, type text}}),
#"Merged Queries" = Table.NestedJoin(#"Trimmed Text", {"UPC"}, Table.Distinct(#"VLookup_Weekly Division", {{"UPC", Comparer.OrdinalIgnoreCase}}), {"UPC"}, "VLookup_Weekly Division", JoinKind.LeftOuter),
#"Expanded VLookup_Weekly Division" = Table.ExpandTableColumn(#"Merged Queries", "VLookup_Weekly Division", {"GROUP", "PRICING GROUP"}, {"GROUP", "PRICING GROUP"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded VLookup_Weekly Division",{"WEEK_NAME", "ITM_SCN_DEPT_DESC", "ITM_SCN_CDTY_DESC", "ITM_SCN_SUB_CDTY_DESC", "MFR_CD", "MFR_DESC", "RPT_SHORT_DESC", "ITEM_DESCRIPTION", "UPC", "GROUP", "PRICING GROUP", "SCANNED_RETAIL_DOLLARS", "SCANNED_MOVEMENT", "SCANNED_LBS", "GROSS_MARGIN_DOLLARS", "AVG_RETAIL", "ITM_SCN_PRC_GRP", "ITM_SCN_PRC_GRP_DESC"})
in
#"Reordered Columns"
Hi Anonymous
That file is a macro enabled workbook, but there are no macros inn it so you could just save it as a .xlsx
Looking at the 2 queries in the workbook, both are set to Connection Only. In the worksheet, right click on the query and then Load To
You'll see it'sset to Only create connection
Select Table and then choose to put the table on an Existing worksheet or a New worksheet.
Regards
Phil
8 Replies
- PhilipTreacy
Super User
Hi Anonymous
That file is a macro enabled workbook, but there are no macros inn it so you could just save it as a .xlsx
Looking at the 2 queries in the workbook, both are set to Connection Only. In the worksheet, right click on the query and then Load To
You'll see it'sset to Only create connection
Select Table and then choose to put the table on an Existing worksheet or a New worksheet.
Regards
Phil
PhilipTreacy Thanks so much! Still learning Power Query
That file is a macro enabled workbook, but there are no macros inn it so you could just save it as a .xlsx
I have my default save type set as .xlm due to using macros alot.
Looking at the 2 queries in the workbook, both are set to Connection Only.
-Where can I see that both queries are set to Connection Only?
-It's strange that it was a Connection Only, but there is a Table to be refreshed..? How did I manage that? lol
(I ask because when I follow your instructions, and try to use Exsisting worksheet, I get an error: Query Results cannot overlap a table or XML mapping. Please select another destination.)
Hello, I do not get the Load To option on my menu. Having the same issue, where the Query is updating but the worksheet is not. The original query was to a different .CSV file. I updated the names and paths, and have done multiple 'Close & Load' and Refreshes both in query and on the worksheet.
- PhilipTreacy
Hi Anonymous
Is the query set to Connection Only? Make sure it is sending its result to a table in Excel. In the Power Query Editor, select the query, and choose 'Close & Load to...' fromthe top left icon,then make sure it is loading to a table.
Regards
Phil
- BA_Pete
Hi Anonymous ,
1) Make sure that the table is loading to a table in Excel. If not (i.e. it says 'Connection only' under Queries and Connections pane), then go back to Power Query Editor, select the query, and choose 'Close & Load to...' from the top left of the Home tab ribbon and select Table.
2) Make sure that you have hit 'Close & Load' from the top left of the Home tab ribbon.
3) If it's still not updating your Excel table, right-click anywhere on the output table and select 'Refresh'.
Pete
BA_Pete PhilipTreacy The data was loaded as a Table, not a 'Connection Only'.
-When I go into the Power Query Editor, the 'Close & Load To' option is greyed-out.
-When I right-click anywhere in the table, 'Refresh' is also greyed-out.
Here is a copy of the file
https://udfinc-my.sharepoint.com/:x:/p/cday/Eeb9AoWu8k9IjLZ5MCNI4FkBa7QBpZSJIlh2Q-OKZ0metQ?e=wjwOG8
- PhilipTreacy
Hi Anonymous
If you look at my last post I explain how to check if it's Connection Only. Right click on the query then click Load To. If the next pop-up box has Only Create Connection selected then the query is Connection Only.
The error message about Query results cannot overlap ...... is because when you selected Table here
the active cell in Excel will be inside another table. You have to click on a cell outside any other table to indicate where you want to load the table.
Phil
Hello, I have the same problem.
My data source is a folder with excel files. The excel files were named as month dates and when I append it in power query. Months in 2022 is not added.
Hope you can help me.
