It will return data to Excel with modified changes. Click “Close & Load” under the “Home” tab.So, this will make the first row a column header.To make the first row a header, under the “Home” tab, click “Use First Row as Header.”.It will open the “Power Query Editor” tab.Click “Edit” under the “Query” tab to apply these changes.There is another problem here, i.e., the first row is not captured as a column header.It will refresh data with updated two new rows. Under “Query,” click on the “Refresh” button.It will show two more tabs: “Query” and “Table Design.” Now, come to Excel and select the table.So, go to the “Text File Data” and add two extra data lines. Once the data is loaded to Excel, the connected text file should be intact Excel.On the right side, we have a window called “Queries & Connections,” which suggests that data is imported through a Power Query. Click “Load” at the bottom, which will load data to an Excel file in Excel table format.It looks like this.Īs you can see above, it has automatically detected the delimiter as a “Comma” and segregated the data into multiple columns. It will display the data preview before it loads to the Power Query model.Now, it will ask you to choose the file you would like to import, so choose the file and click on “OK.”.

Go to the “Data” tab, and under “Get Data,” click on “From File.” Under this, click on “From Text / CSV.”.We will use a Power Query to import this data and transform it into a format that Excel loves working with.

Get and transform excel 2016 download