Power Query Data is updating in PQ Editor, but not in Excel Table

[Anonymous]

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"

[raquino]

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. 

[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

[PhilipTreacy · Accepted solution]

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 

[Anonymous]

@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.)

[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

[Anonymous]

@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 

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