|
⇤ ← Revision 1 as of 2025-07-18 14:14:22
Size: 2066
Comment: Initial commit
|
Size: 2062
Comment: Update links
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 3: | Line 3: |
| 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 [[Excel/PowerQueries|Power Queries]]. | 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 [[Excel/PowerQuery|Power Queries]]. |
| Line 18: | Line 18: |
| To create a table from a structured data file, e.g. a JSON file, selected Get Data > From File > From JSON. A file picker popup leads into the [[Excel/PowerQueries|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 a structured data file, e.g. a JSON file, selected Get Data > From File > From JSON. A file picker popup leads into the [[Excel/PowerQuery|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. |
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 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, selected 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 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 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.