Introduction to Career Skills in Data Analytics

Your "Subtotal" field is showing as left aligned and without a currency symbol. What is likely the issue?

The field is formatted as text

When modeling data in Power BI, which process can help the data analyst, but should not be relied upon until it's proven trustworthy?

Use the Auto Detect Relationships feature

Conditional formatting is an example of _____.

identifying data

What type of information is best displayed in a paginated report?

list of invoices for the year

Which options are more flexible at storing data than a spreadsheet? A. database B. data warehouse C. CSV (comma separated values)

A and B

You are a fluent data professional. Which option best describes data fluency?

You can create tools, reports, visuals, and data sets.

What feature in Power BI automatically attempts to detect relationships?

AutoDetect

When a field does not show the type of data that you need, what transformation will you perform?

Change the data type

Which option is a language in Power BI for creating the calculations in Quick Measures?

DAX

What is an example of a visual element that should be included where appropriate in a dashboard?

tooltip, data label, visual title

What are the two core functions that Power BI offers to data analysts?

transformation of data and presentation of data

A PDF of an organization's annual report would be considered _____.

unstructured data

Which option describes parsing text?

breaking parts of text out of a field to create fields

In using Power Query, you notice that a field of data is not joining with data that appears the same. You have already removed trailing and leading spaces. What cleaning command should you consider next?

CLEAN

Which option best describes effective data governance?

Data governance is a framework that incorporates strategies to create secure and quality data, enable accountability, and provide transparency of data in the organization.

A report needs to show the number of weekdays within a project to estimate capacity. Which function would provide the desired result?

NETWORKDAYS

The inventory dataset has a field for "Inventory ID." The first two letters signify whether the inventory is received or not received, followed by a hyphen, and then the 5-digit ID number (e.g. IR-45322). For the inventory report, you want to create a calculation that only provides the numbers after the hyphen. Which function would give you only the digits from the Inventory ID?

=Right (Inventory ID, 5)

What is an example of a business rule for data?

A bundle sale is defined as a minimum of 3 products sold at the same time.

You have a quarterly sales data set that contains a date field, with each business day written as a value for orders placed on that day. You need to find the days on which sales did not occur, and you do not have every day of the time period in your data. What should you introduce into your model?

Add a date table joined to the data set. The join should provide all dates in the time period and the associated records

What is an example of Master Data Management rules?

All two letter abbreviations for State/Region/Province names must be in all caps, Each department name will include the department number, Each contact name shall include first name, last name, and middle initial.

A "City" field has some values written as uppercase and other values as lowercase, as well as inconsistent spellings. Which processes would create a field with uppercase values that are correctly spelled? A) =TRIM B) Replace Values C) =UPPER

B and C

For an organization to be truly data-informed and make data-driven decisions, which option(s) would give them the full picture of their data? A) spreadsheets B) business intelligence C) business analytics D) data analytics

B, C, and D

You're creating a PivotTable and a number field titled "ProductID" defaults to the SUM aggregate function. Which transformation can you apply so that it defaults to COUNT instead of SUM?

Change the value to COUNT in the Value Field Settings.

A data analyst prepares a report from a monthly uploaded data set. However, in their initial profile of the data, one of the historically top-selling products is not included. Which action item would be best practice?

Communicate with the person or team responsible for the dataset, letting them know that the product is missing

You need to create a status field in Power Query for any order that has 30 days between the start of the order and the ship date. It will provide a status value of "Over 30 Days." Which built-in function can you use?

Conditional Column

A product has an updated weight value after a manufacture change, and the inventory report has an inconsistent total value for the calculated "Cost" field. What should be performed to resolve the issue?

Confirm the calculation is updated to reflect the change

When profiling a dataset for the first time, you note that a row is blank in the first column. What should be your first action?

Confirm the row is empty

An organization has a table visual with customer and total sales data. They want to be able to see each product ordered by the customers, as well as expand/collapse various customers when using the visual. Which process would provide this solution?

Convert the visual to a matrix with the product transactions field in both the rows and values

You are working with a data set including order records and their ID numbers. While profiling the data, you noticed that there are duplicate values in the "ID" field for each order. You need to create a data set that only shows one record per order, which would create a data set with distinct orders. What keyword can you include in the query to provide a unique list of orders?

DISTINCT

An annual review of total compensation shows one total in the payroll database and a different total in the HR software. What should be your next step?

Determine which system has the accurate value for total compensation (system of record).

When you desire a unique count, which function should you use?

Distinct Count

When working with data in Excel, what is your first step?

Ensure that the entire data set is recognized by Excel.

A data worker will likely have more access to data and a stronger understanding of relational databases than a data analyst, data engineer, or data scientist.

FALSE

Mockups help communicate the navigation of information.

FALSE

RDBMS stands for relational database machine systems.

FALSE

To be a data analyst, you must be able to manually calculate every single math calculation you need.

FALSE

Tools alone can provide business intelligence to an organization.

FALSE

What type of command do slicers perform in a Power BI Dashboard?

Filter

A company has determined that October is the month when their sales are the highest. They are looking at providing discounts to increase their profits for the seasonal high-point. What other questions should drive this business decision?

Is there a produce that has a higher margin that sells more in October? Is there more profit in October, or just more sales? How many years has October been the highest in sales?

Your boss requests that you create a dashboard that includes a visual for your company's annual sales, with months by top 10 products. Which visualization would not be appropriate?

KPI

Metrics that inform the business on the overall health of the organization, processes, or products are considered _____.

KPIs (Key Performance Indicators)

KPIs are defined as _____.

Key Performance Indicators, targets for business intelligence, metrics that track the overall health of the organization

What is not a type of cardinality?

One-to-none

When working with data that requires transformations, which option provides you with the most robust options in Excel?

Power Query

A company is looking into increasing their margins on a product. Which variable(s) could be taken into consideration? A) Product Cost B) Product Retail Price C) Customer Location

Product cost and product retail price

What should you do with every new data set before you begin to analyze the data?

Profile the data.

What is the benefit of quality data?

Quality data can be trusted to produce accurate insights so decisions can be made.

An "Orders" table has 30 columns, and the report only needs the order total by date. Which transformation should you perform?

Remove the other columns. Group by using the date column and aggregate the appropriate total field.

Which statements are required in a basic SQL query?

SELECT FROM

Which transformation can you use to remove leading and trailing spaces in a field?

TRIM

Business analytics helps you to see trends and predict future outcomes.

TRUE

Spaces can be used as delimiters.

TRUE

There is a "Full Name" field with last name and first name separated by a comma. How can you parse the field into "Last Name" and "First Name" fields?

Text to Columns

You are working with two tables, called "Products" and "Freight Cost," to develop a new report. The report should return a data set that includes all products and their associated freight costs, including those products that have zero freight costs. However, you notice that the report is only returning the products with positive freight costs associated. The report appears to be missing products, although they do exist in the "Products" table. What could be causing this?

The Join type is set to Inner

The monthly payroll system contains every pay period from the start of the year and is updated weekly. This data will be used to write a weekly payroll report. However, when working with the data, the date field for payroll date does not appear to have a date hierarchy. What is most likely?

The data type for the payroll date is not set to a date data type

What can be customized to provide more insights into a visual when you hover over items?

Tooltips

You notice that the numbers for your data project do not seem to match what the team expects. What is the first thing on your task list?

Verify your data set to ensure you have all necessary data and that you have not introduced an error.

You have been tasked to study product profitability. What questions should you attempt to answer at the start of the project?

What is the current cost of these products? What is the current sales price of these products? What is the current margin of these products? Which of these products has been profitable?

The leadership of an organization has commissioned a study on data about the sales team. The goal of the project is to determine top performers and their attributes. After gathering data and establishing basic rules and requirements, what is the most reasonable question to explore first?

Who are the top performing salespeople for any given point in time?

As a data analyst, what is the term for detecting different trends and patterns to help improve overall performance?

business analysis

Which visual in Power BI is good for displaying a single aggregated data point?

card

What is not an example of data soft skills?

cleaning a data set with Power Query

What are hallmarks of data quality?

completeness, consistency, accuracy

A(n) _____ translates large amounts of data sets in many tables, and maybe across multiple systems, into data sets that others use for their reporting.

data engineer

What is a framework that incorporates strategies to create solid quality data, enable accountability, and provide transparency to data in the organization?

data governance

Determining the count of values and basic transformation requirements in a data set is known as _____.

data profiling

Which process includes determining the volume of data, identifying basic information of the different field values, and uncovering basic transformation requirements in a data set?

data profiling

Which role requires more advanced math, statistics, and coding skills?

data scientist

What is the most universal data role?

data worker

You are using all available information to help drive decisions for an organization. Which phrase describes this action at a high level?

data-driven decision making

Data flows through the organization at different times. Because systems are often disconnected, what is the first challenge you have to overcome as a data analyst?

discovering which systems have the most accurate information at any given time

What is a critical component of every data role?

documentation

You need to create a field that provides the value "over" or "under" for sales, depending on whether the amount is greater than or equal to 15,000. Which type of function can you write to create this data?

logical functions, such as IF, CONDITIONAL COLUMN, or IIF

A quarterly report is created within Power BI using a dataset with four fields: Sales, Customer Name, Customer ID, and Salesperson. The report only needs to show three fields: Sales, Customer Name with the Customer ID (e.g. "Brakes and Shakes (15)"), and Salesperson. The customer field in the report includes the Customer Name and Customer ID. Which transformation can you use to create the "Customer Name (CustomerID)" field for the report?

merging columns

Power BI Report Builder and SSRS are both tools for _____.

paginated reports

Data that fits neatly into tables with fields and values is _____ data.

structured

When data in a software system is considered the most valid record and represents the truth of data, how would you classify that data?

system of record

What shows by default on the tooltip for any visual you create?

the fields used for the visual