Learn how to import data into Excel using the Get and Transform function with Power Query. The most common cause is that you don’t store to be processed data in Excel.
It is not surprising that we seldom store databases and a large amount of raw data in Excel worksheets. The main reason is simple: Excel was not made for this.
You can import it from an external data source. Let’s see an example of data import in Excel. Use Excel’s Get & Transform function (with the help of Power Query) to connect to a comma-separated values (.csv) file.
Using external data sources seems to be a rational decision when raw data is not in Excel. Using Power Query editor, we can get and transform our data quickly.
In the example, we store the data in a CSV file. To import data into Excel from a file, do the following steps.
Steps to import data into Excel
1. Click on the Data tab on the ribbon. Choose ‘Get Data’ > ‘From Text / CSV’ > ‘From File’ > ‘From Text/CSV’
2. In the Import Data dialog box, locate the file that you want to query. Click Import.
3. If you are working with a CSV file, Power Query will automatically detect column delimiters. Power Query automatically uses the first row as the column names and changes each column data type.
If you want to edit your data ‘on the fly’ click to edit. A Power Query Editor will appear.
Click Load To…
4. Choose a layout that is best for our project.
- Excel Table
- Pivot Table Report
- Pivot Chart
- Only create a connection
- Add this data to the Data Model
Most useful to apply the tabular format (Excel table). Let’s choose this option!
5. Click OK
Excel gets the external data range in the location that you specify. Our data transformed into a table. Why it’s so important? We’ll answer this question in the next chapter.