Excel Get and Transform Data
The primary section of the Data Ribbon is Get and Transform Data. These are a set of tools for creating a table that can be transformed with Power Queries.
Contents
Usage
The Data > Get Data dropdown menu offers a series of import procedures, ranging from From File to From Azure. Notably:
grouped under From File are From XML and From JSON
grouped under From Database is From Microsoft Access Database
grouped under From Other Sources is From ODBC
To create a table from a structured data file, e.g. a JSON file, select Data > Get Data > From File > From JSON. A file picker popup leads into the Power Query editor, where the file has been parsed into a table of names and values. A hierarchical JSON file will be presented as a table of names and records; selecting a record's link brings up a new table. This process is analogous to a file explorer.
To create a table from an external data file, e.g. a CSV file, select Data > From Text/CSV in the Get and Transform Data section. A file picker popup leads into an import wizard. The wizard offers several options for parsing the file, and provides a truncated data preview. At the bottom of the popup, there are buttons for Load and Transform Data. The former inserts the parsed data into a new sheet, named like the source file, within the workbook. The latter brings up the Power Query editor.
To create a table from data that is already present in the Excel workbook, select Data > From Table/Range in the Get and Transform Data section. A Create Table popup asks for the cell range containing the data, and whether there are headers for that data. If a cell range was already selected when the From Table/Range button was clicked, that range is pre-populated into the popup. Confirming these options with Ok brings up the Power Query editor.