= 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 [[Excel/PowerQuery|Power Queries]]. <> ---- == 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 [[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. 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. ---- CategoryRicottone