Using the Power Query Add-In - Microsoft Business Intelligence Tools for Excel Analysts - page 139

Table 7-2 lists each table-level action along with its primary purpose.

Many of the table actions have corresponding commands on the Query Editor's Ribbon.

Tip

Table 7-2: Table-Level Actions

Action Purpose

Use First Row As
Headers

Replaces each table header name by the values in the first row of each column.

Remove Duplicates Removes all rows from where the values in the selected columns duplicate earlier

values. The row with the first occurrence of a value set is not removed.

Remove Errors Removes rows containing errors in the currently selected columns.

Insert Custom Column Inserts a new column after the last column of the table. The values in the new

column are determined by the value or formula you define.

Insert Index Column Adds a new column containing a sequential list of index numbers starting from 0.

Keep Top Rows Removes all but the top N number of rows. You specify the number threshold.

Keep Top 100 Rows Removes all but the top 100 rows.

Keep Range of Rows Removes all rows except those that fall within a range you specify.

Remove Top Rows Remove top N rows from the table.

Remove Alternate Rows Remove alternate rows from the table starting at first row, specifying the number of

rows to remove and the number of rows to keep.

Merge Creates a new query that merges the current table with another query in the work-

book by matching specified columns.

Append Creates a new query that appends the results of another query in the workbook to

the current table.

Power Query Connection Types
Microsoft has invested a great deal of time and resources in ensuring that Power Query has the ability
to connect to a wide array of data sources. Whether you need to pull data from an online search, an
external Web site, a file, a database system, SharePoint, Facebook, or a big data source such as
Hadoop, Power Query can accommodate most, if not all, your source data needs.

The available connection types are in the Get External Data group on the Power Query tab. As shown
in Figure 7-15, Power Query offers these categories of connection types:

➤ Online Search: Pulls data from a Web source based on a specified search term.

➤ From Web: Pulls data from a Web site based on a specified URL.

➤ From File: Pulls data from a specified flat file or folder.

➤ From Database: Pulls data from a relational database system.