How to convert imported data to a table while preserving external connections

I am new to this forum and this is my first post. So please bear with me if my question has already been addressed elsewhere, as I expect it to be a fairly common issue. I have imported a csv file as an Excel (version 2016) range via "Data->From Text" option, and now trying to convert it to a table. But I always get "Your selection overlaps one or more external data ranges. Do you want to convert the selection to a table and remove all external connections?" Choosing "No" does not give me the table, but I do not want to say "Yes", because I'd like to keep this connection, so I can use "Refresh All" to quickly incorporate any future changes to the original csv file into all subsequent charts etc I create using this table.

Thanks,
Manojit

Thanks for the quick reply!

If your importing this into a new blank file just hit yes. It seems more likely you are trying to import it into an existing sheet, in which case you likely have content on the sheet that refers to an outside data source in some fashion (named range, conditional format, hyperlink, invisible content, etc).

The csv itself isnt the data connection, its some content that Excel perceives as already being in the destination that the imported content will go. This usually means it doesnt matter what you try to put in those cells or how, you would still destroy whatever was there, warning or not.

The other option would be to open the csv with Excel as a seperate file and copy/paste to your desired destination.

Thanks. I was importing into a new sheet (not existing one), but I was wondering what if later new data get appended to the end of the original csv file - would it update my table etc by hitting Refresh all.

Originally Posted by manojit

Thanks. I was importing into a new sheet (not existing one), but I was wondering what if later new data get appended to the end of the original csv file - would it update my table etc by hitting Refresh all.

This only happens when you create a link to external data and in my experience doesnt work so great when working off a text file like csv. Depending on where the csv comes from you may consider skipping the csv alltogether and just connecting your Workbook direct to the database/source of the csv if thats possible with your data/source.

Alternatively, if the layout of the columns doesnt change from csv to Workbook, you could simply copy CSV content and paste it below the current data (presuming you opened CSV in Excel as a seperate file).

Thanks again. I am somewhat new to Excel, and these posts are quite helpful. As a background to my question, I am working on an assignment that starts with importing a set of csv data into excel tables, and using them to build a dashboard that contains several charts & tables. This dashboard needs to be flexible (dynamic?) so that if new rows get added to any of the original csv files - for example, a client sends updated sales data every few months - I should be able to update the dashboard just by hitting "Refresh All", if at all possible, instead of having to re-work it from the ground up. This is why I wanted to know what is the best way to import the csv data so external connections to them are preserved.

The method outlined in my post will create connection to CSV and will apply any transformation steps to subsequent import.

If the same file is being updated, so will imported table when you refresh. Along with all appropriate transformations.

For an example...
I have dashboard set up connecting to CSV file stored in OneDrive, which is updated from data sent from cloud based CRM to email (unfortunately no direct DB access). MS Flow is used to store attachment to specific folder within OneDrive. PQ then queries the file and imports it into data model for consumption by PowerPivot.

Thanks. Yes, I used your Powerquery method to import the data.