Page 303 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

The Home tab contains all the actions, transformations, and settings that will affect the whole table.
1 Close – You can access the Close & Load and Close & Load To options from here. These are also available
in the File tab menu.

2 Query – You can refresh the data preview for the current query or all query connections. You can also open
the properties settings and the advanced editor for the current query and there are options under the Manage
button to delete, duplicate or reference the current query.

3 Manage Columns – You can navigate to specific columns and choose to keep or remove columns.
4 Reduce Rows – You can manage the rows of data from this section. There are lots of options to either keep
certain rows or remove certain rows. Keep or remove the top N rows, the bottom N rows, a particular range
of rows, alternating rows, duplicate rows or rows with errors. One option only available for removing rows is to
remove blank rows.

5 Sort – You can sort any column in either ascending or descending order.
6 Transform – This section contains a mix of useful transformation options.
• Split Columns – This allows you to split the data in a column based on a delimiter or character length.
• Group By – This allows you to group and summarize your data similar to a Group By in SQL.
• Data Type – This allows you to change the data type of any column.

• Use First Row as Headers – This allows you to promote the first row of data to column headings or demote the
column headings to a row of data.
• Replace Values – This allows you to find and replace any value from a column.
1 Combine – This sections contains all the commands for joining your query to with other queries. You can
merge, append queries or combine files when working with a from folder query.
2 Parameters – Power Query allows you to create parameters for your queries. For example when setting up a
from folder query, you may want the folder path to be a parameter as so you can easily change the location.
You can create and manage existing parameters from this section.

3 Data Sources – This section contains the data source settings including permissions management for any data
sources that require passwords to access.
4 New Query – You can create new queries from new data sources or previously used data sources from this
section.
The Difference Between the Transform and Add Column Tabs
The bulk of all transformations available in power query can be accessed through either the Transform tab or the
Add Column tab.
You might think there is a lot of duplication between these two tabs. For example, both tabs contain a From Text
section with a lot of the same commands. It’s not really the case, there is a subtle difference!
When you use a command from the Add Column tab that is found in both tabs, it will create a new column with
the transformed data and the original column will stay intact. Whereas using the equivalent command from the
Transform tab will change the original column and no new column is created.
The Transform Tab
The Transform tab sections.
1 Table – This section contains commands that will transform the entire table. You can group and aggregate your
query, promote rows to headers, demote headers to rows transpose your data, reverse row order, and count
rows.
2 Any Column – This section contains commands that will work on any column of data regardless of data type.
You can change the data type, automatically detect and change the data type, rename the column heading,
find and replace values, fill values down (or up) a column to replace any blanks or nulls with the value above
it (or below it), pivot or unpivot columns, move columns to a new location or convert a column to a list.

290

CITS : IT&ITES - Computer Software Application - Lesson 63 - 77