Chapter 10: Power Add-Ins: Managing Data

Step 1 of the Importing process of .txt files: IMPORTING DELIMITED FILES

3 step importing process of .txt files: Step 1: select Delimited or Fixed width based on how data are structured in text file (most common is delimiters). *** start import at row: set this value to where you want the data to begin. Look at Preview of File to view data in the text file. My data has headers: select this if your data has headers/title (start import at row below header).

Step 2 of the Importing process of .txt files: CHOOSING DELIMITERS

3 step importing process of .txt files: Step 2: the Text Import Wizard displays 2 options for importing data. - click the appropriate delimiter check box if the text is delimited (click other box and type the specific character if text contains different delimiter). - move the column break lines to where the column begins and ends if the text file contains fixed width columns.

Step 3 of the Importing process of .txt files

3 step importing process of .txt files: Step 3: Select an option in the Column data format section for each column you want to import in the Text Import Wizard. - select the column heading in the Data Preview window and select the option you want in the Column Data format. If you don't want to import column: select the Do not Import column (skip) option.

Power Pivot

A built-in add-in that offers the key functionality that is included in excels standard PivotTable options, plus a variety of useful features for power (advanced business) users. - key features: handling/compressing big data files, identifying/displaying key performance indicators, etc. Load Power Pivot Add-In: - click the File Tab and click options. - select add-ins and click COM Add-ins in the Manage box. - click go, select Microsoft power pivot for excel, and click OK.

Data model

A collection of related tables that contain structured data used to create a database, enables you to integrate data from multiple sources within a workbook. Data from this can be aggregated into a PivotTable using Power Pivot. External data can be added to the model during the import process.

Fixed-width text file

A file in which each column contains a specific number of characters, ex. 5 characters per column.

Create Relationships with Power Pivot

A key feature of power Pivot is that you can create relationships among multiple data sources that share common fields. Relationship: an association or connection between two related tables were both tables, contain a Related field of data, such as IDs. To create a power pivot relationship: 1. Click Power Pivot tab and click Manage. 2. Click Design Tab and click Create Relationship. 3. Click the Table 1 arrow and select the name of the primary table. 4. Select the name of the column that contains a relationship to the related or lookup table in Table 1 columns box. 5. Click the Table 2 Arrow and select the name of the related or lookup table. 6. Click the table 2 columns box, select the name of the column that is related to the primary table, and click OK. TIP: Power Pivot is only available for Microsoft office business premium. Non-premium subscribers can create relationships between data sources by clicking the Data Tab and selecting Relationships in the Data Tools group.

Data visualization

A method of summarizing data visually to better understand the significance of the information.

Tab-delimited file

A technique that uses tabs to separate data into columns. - columns do not align (only 1 tab separates columns).

Creating a PivotTable and PivotChart using Power Pivot

After you create a relationship between the tables, you can use power pivot to create a pivot table or pivot chart. To create a PivotTable using related tables: 1. Click the Power Pivot tab and click Manage. 2. Click PivotTable on the Power Pivot Home Tab and select the PivotTable or PivotChart options. 3. Select an option to either create the PivotTable or PivotChart on a new or existing worksheet. 4. Click OK.

3D Map

An add-in that plots geographic and time data on a world map. -overlays geographic data on a custom map or general world map.

Power BI

An online application suite designed to help users manage, supplement, visualize, and analyze data. Popular features: 1. Power Pivot 2. 3D maps

Geographic information

Attached image details the geographic information that can be visualized using 3D maps.

Load Power Map Add-in

Before creating a 3D map, you must first load the add-in: - click the file tab and click options. - select add-ins and click COM add-ins in the Manage Box. - select Microsoft Power Map for excel.

Transform Data

Data Shaping: the process of editing information from one or more data sources to desired parameters before importing into an application. When using Get & Transform, data is shaped by the Power Query Editor (a business intelligence tool that can connect and shape data from multiple sources). To access Power Query Editor: - click Transform Data after previewing the data during the import process. OR - click Edit in the Edit group on the Query tab. Query Navigator Pane: when the Power Query Editor is active and there are multiple queries in a workbook, you can expand and collapse views. *** in attached image.

Add dimensions

Dimensions: attributes that contribute to the visualization of data. Possible dimensions of map: -location (location info) -height (total inventory) -category (types of inventory) -time To add dimensions: - drag the field from the Field List to the appropriate dimensions box within the layer. To add additional layer: - click Add Layer at the top of the Layer Pane.

Set external data properties

External data properties: setting that controls how imported data connects to the external data source. *** specify how the data displays in Excel, how often the data is refreshed, and what happens if the number of rows in the data range changes. To view and edit external data properties: - click Properties in the Queries and Connections group to display the External Data Properties dialog box. - then select the icon next to the Name box to open Query Properties.

Import an Access Table or Query into a excel

Large amounts of data are often stored in databases, such as an Access database table. However, database programs are not intuitive about manipulating data for quantitive analysis, or do not contain the capabilities to do so. To import an Access Database table or query into excel: 1. Start Excel, start a new workbook or open an existing workbook, and then click the worksheet tab to which you want to import the data. 2. Click the Data Tab and click Get Data in the Get & Transform data group. Select Database and select Microsoft Access Database. Browse to the data base and select Import. 3. Choose a table or query from the list in the Navigator Pane and click Load to directly import the database object or Edit to open the Power Query Editor.

Edit Query Properties

Query properties determine the frequency in which the data refreshes as well as a variety of advanced online analytical processing settings. Query properties can be accessed by clicking the Query Properties in the External Data Properties dialog box. OR - right click the active query in the Queries and Connections pane and select Properties.

Refresh

Refresh: updates the linked data in an excel workbook with the most up to date information. To ensure that excel contains the most current data: you must refresh connections to external data sources periodically. To refresh all: click Refresh All in the Queries and Connections group. To update a single data source: select the range containing the external data and click Refresh All arrow. Select Refresh. OR - right click in a range of data and select Refresh to update the data.

Get Data from a Text/CSV file

Text file: a data file that contains characters, such as letters, numbers, and symbols, including punctuation and spaces (does not contain formatting, sound, images, video). *** .txt file extension. Contain: - delimiters: special characters (such as tab or space) that separate data. To get data from text or CSV files: Use Get & Transform tools located on the Data Tab in the Get & Transform group. - to import text or CSV files: click from Text/CSV on the Data Tab. *** then choose files origin, delimiter, and data type detection. *** then click Load to import data into a table.

View Current Queries

The Queries & Connections group on the Data tab contains options to manage external data connections. To review active queries or external program connections: click the Data tab and click Queries and Connections in the Active Queries and Connections group. *** this opens the Queries and Connections pane that displays active queries and external program connections.

Embed

The act of importing external data into Excel but not maintaining a link to the original source. - can edit data directly within cell because there is no connection to the original data. - changes in original data will not change the excel data and vise versa.

Importing

The process of inserting external data (data created or stored in another format) into the current application. Use Get & Transform tools to import data to excel from sources. *** located on the Data tab. When using get and transform tools: - the data is placed in a table and a link fo fhe orginal data source is automatically created (refresh excel if any changes are made to original data).

Create 3D map

To create a 3D map based on geographic information, first decide how you want to visualize the data. Example: if you want to map a flight plan, organize each stop of the flight in a table, based on longitude and latitude, or city and state. Excel can create multiple 3D maps per workbook, each map is called a tour. Each tour contains scenes of geographic visualizations that are organized by layer. Create a 3D map: - select range containing data to be added to map. - click insert tab, click 3D maps, and select Open 3D maps. - then add dimensions.

To edit the query data source

To edit the query data source: 1. Double click the query (or right click the query) in the Queries and Connections pane and select Edit to open the Power Query editor. 2. Click the data source settings in the Data Sources group and select Change Source. 3. Browse to the location of the file, set the preference as shown in attached image and click OK.

To remove connection

To remove a connection you no longer want to link the data to the external data source: - select the table containing the imported data and click the Design Tab. - next, click Unlink and click OK in the warning message box. No longer able to refresh once disconnected.

Create a Tour

Tour: a time-based animation that can display a 3D map from different perspectives. - broken down into animated segments dallied scenes. By default, 3D maps contain 1 scene. Scenes can be added by clicking New Scene in the scene group on the 3D map ribbon. - must be edited (will add layer information from previous scene). To create a tour: - add scenes by clicking New Scene in the Scene group of the 3D map ribbon. - after adding scenes, click Play Tour in the tour group.

Comma Separated Values (CSV) file

Uses commas to separate data into columns and a newline character to separate data in rows. Newline character: a special character that designates the end of a line and separates data for the next line or row.

Transform tools

When data is loaded into the Power Query Editor, it displays as a table. Each column/row can be formatted, appended, removed, or split. ***can also add data by adding custom calculated field. To format data: - click the column of data you want to format. - click the Home Tab on the Power Query Editor Ribbon and select options in transform data (attached image contains options). - click Close & Load in the Close group to exit Power Query Editor. To add/remove columns: - click the Home Tab on the Power Query Editor ribbon, and select Choose Columns in the Manage Columns group. Once data is edited: click close & load in the Close group to import the data into the worksheet.

Query Settings Pane

When the data is shaped using the Power Query Editor, the Query Settings Pane displays the name of the query and the steps taken to shape the data. Can undo steps: by pressing the X next to the step in this pane.

Add data to data model

When using the Get & Transform tools to import data into a data model, click the Load Arrow and select Load To which opens the Import Data Dialog box. - check Add this data to the Data Model and click OK. This imports the data into a workbook and adds connections to the existing data model (or a new model will be created with the active connection). If you want to add data that has already been imported into a worksheet to a data model: - select the data - click power pivot tab - and click add to data model in the tables group.

Import Data from other Sources

You can import from sources other than Text Files and Access Databases. Do this by: clicking Get Data in the Get & Transform data group and click From Other Sources to display a list of additional sources.