Home

Refreshing Data Query when column name changes

%3CLINGO-SUB%20id%3D%22lingo-sub-900363%22%20slang%3D%22en-US%22%3ERefreshing%20Data%20Query%20when%20column%20name%20changes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-900363%22%20slang%3D%22en-US%22%3E%3CP%3EOpened%20new%20work%20book.%3C%2FP%3E%3CP%3EWent%20to%20the%20data%20tab%20and%20clicked%20on%20recent%20sources.%3C%2FP%3E%3CP%3ESelected%20%22http%3A%2F%2Fwww%20thefinancials%20com%2Fsyndicated%2FFree%2FEX_Interest_TBills%20html%22%3C%2FP%3E%3CP%3ESelected%20connect.%3C%2FP%3E%3CP%3EOn%20Navigator%20window%20selected%20table%2C%20then%20selected%20load.%3C%2FP%3E%3CP%3ENext%20I%20select%20the%20Query%20Menu%2C%20then%20I%20select%20edit.%3C%2FP%3E%3CP%3EThe%20%22Power%20Query%20Editor%22%20opens.%3C%2FP%3E%3CP%3EThe%20right%20side%20of%20the%20window%20shows%20Query%20Settings.%3C%2FP%3E%3CP%3EI%20click%20on%20the%20X%20next%20to%20Changed%20Type.%20(%20this%20was%20done%20in%20so%20that%20when%20I%20opened%20the%20file%20the%20next%20day%2C%20the%20data%20would%20load%20even%20though%20the%20column%20names%20have%20changed)%3C%2FP%3E%3CP%3EI%20close%20the%26nbsp%3B%22Power%20Query%20Editor%22%20and%20keep%20changes.%3C%2FP%3E%3CP%3EBack%20at%20the%26nbsp%3BQuery%20Menu%2C%20I%20select%20properties.%20On%20the%20Query%20Properties%20window%2C%20usage%20tab%2C%20I%20put%20a%20check%20mark%20in%20%22Refresh%20data%20when%20opening%20the%20file%22%2C%20then%20select%20%22OK%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20imported%20data%20from%20the%20website%20is%20on%20%22sheet2%22%20of%20the%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20switch%20to%20%22sheet1%22%20and%20insert%20the%20following%20formula%20in%20cell%20A1%2C%26nbsp%3B%3Dsheet2!B3.%20In%20cell%20B1%2C%20I%20insert%2C%26nbsp%3B%3Dsheet2!C3.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20in%20lies%20the%20problem.%20Today%20the%20information%20on%20sheet1%20is%20correct%20but%20tomorrow%20when%20the%20data%20is%20refreshed%2C%20the%20data%20in%20A1%20will%20be%20in%20B1%20and%20A1%20will%20show%20%23REF!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPart%20of%20this%20is%20because%20on%20%22sheet1%22%2C%20cell%20A1%20changes%20to%20%3Dsheet2!C3%20when%20the%20file%20is%20opened%20the%20next%20day.%20I%20am%20going%20to%20try%20including%20the%20%22%24%22%20in%20the%20source%20cell%20to%20see%20if%20this%20will%20work%20tomorrow.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20other%20suggestions%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-900363%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-901658%22%20slang%3D%22en-US%22%3ERe%3A%20Refreshing%20Data%20Query%20when%20column%20name%20changes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-901658%22%20slang%3D%22en-US%22%3EDoes%20it%20help%20to%20select%20the%20table%20and%20click%20the%20Properties%20button%20on%20the%20data%20tab%20of%20the%20ribbon%20and%20choose%20the%20last%20option%20%22Overwrite%20existing%20cells%20with%20new%20data%2C%20clear%20unused%20cells%22%3F%3C%2FLINGO-BODY%3E
Dennis26838
Occasional Visitor

Opened new work book.

Went to the data tab and clicked on recent sources.

Selected "http://www thefinancials com/syndicated/Free/EX_Interest_TBills html"

Selected connect.

On Navigator window selected table, then selected load.

Next I select the Query Menu, then I select edit.

The "Power Query Editor" opens.

The right side of the window shows Query Settings.

I click on the X next to Changed Type. ( this was done in so that when I opened the file the next day, the data would load even though the column names have changed)

I close the "Power Query Editor" and keep changes.

Back at the Query Menu, I select properties. On the Query Properties window, usage tab, I put a check mark in "Refresh data when opening the file", then select "OK".

 

The imported data from the website is on "sheet2" of the workbook.

 

I switch to "sheet1" and insert the following formula in cell A1, =sheet2!B3. In cell B1, I insert, =sheet2!C3. 

 

Here in lies the problem. Today the information on sheet1 is correct but tomorrow when the data is refreshed, the data in A1 will be in B1 and A1 will show #REF!

 

Part of this is because on "sheet1", cell A1 changes to =sheet2!C3 when the file is opened the next day. I am going to try including the "$" in the source cell to see if this will work tomorrow.

 

Any other suggestions would be appreciated.

 

 

 

1 Reply
Does it help to select the table and click the Properties button on the data tab of the ribbon and choose the last option "Overwrite existing cells with new data, clear unused cells"?
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies