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
Highlighted
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
Highlighted
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
Some of the latest Edge Canary changes
HotCakeX in Discussions on
0 Replies
History and Data
rosenbloomsnka in Microsoft To Do on
0 Replies
Problem with sharepoint refreshing
Sibuns123 in SharePoint on
4 Replies