Part V: Understanding Power Pivot and Power Query
To import a text or CSV file, go to the Excel Ribbon and select Data ➪ Get Data ➪ From File
➪ From Text/CSV. Excel will activate the Import Data dialog box where you can browse for
and select a text or CSV file.
Excel has another From Text/CSV button on the Data tab next to the Get Data command. This duplicate command is
actually the legacy import capability found in all Excel versions.
The Power Query version is much more powerful, allowing you to shape and transform text data before importing.
Make sure you are using the correct Power Query version of the From Text/CSV feature.
Power Query will open the Power Query Editor to show you the contents of the text or CSV
file that you just imported. The idea here is to apply any changes you want to make to the
data and then click the Close & Load command on the Home tab to complete the import.
Some text files are structured as tab-delimited files. Similar to CSV files, tab-delimited text files contain tab char-
acters that separate text values into columns of data. Power Query will recognize tab-delimited text files and import
these files into a table that contains a separate column for each tab delimiter.
Power Query is good at recognizing the correct delimiters in CSV files and typically does a
good job of importing the data correctly.
For instance, row 5 in the sample CSV file illustrated in Figure 38.18 contains the value
Johnson, Kimberly. Power Query contains the intelligence to know that the comma in
that value is not an actual delimiter. So, all of the columns are separated correctly.
FIGURE 38.18
CSV files are brought into the Power Query Editor where you can apply your edits and then
click the Close & Load command to complete the import.
