Get Excel Power Tools for Data Analysis Coursera Quiz Answers
Excel Power Tools for Data Analysis Coursera Quiz Answers
Week 1: Excel Power Tools for Data Analysis
Quiz 1: Introduction to Power Query
Q1. Which of the following sources does Power Query allow us to Get Data from?
- A folder.
- An online service.
- A database.
- A JPEG.
- A PDF.
Q2. What would happen if you tried to create a query from data in the current workbook that is not part of a table or a named range?
- If the selected data is not part of a table or a named range, it will be converted into a named range.
- Power query will still be able to perform the requested operations without any conversion.
- An error message will appear.
- If the selected data is not part of a table or a named range, it will be converted into a table.
Q3. In Australia, for a phone number such as 0223789456, the first two digits represents the area code. Consider a field that contains phone numbers in this format. What would be the appropriate option under Split Column to extract the area code?
- By Number of characters
- By Position
- By Delimiter
Q4. If you are utilising Power Query primarily as a ‘working space’ without viewing the data in your spreadsheet, within Close & Load to, you should choose the option:
- Only Create Connection
- PivotTable Report
Q5. After creating a new Table via Power Query, what would happen when the original data is edited or changed?
- The new Table will be automatically updated.
Quiz 2: Get and transform data from Excel
Q1. When getting data from another workbook, it is essential to transform the data within that workbook first.
Q2. What are some differences between Power Query and standard Excel?
- You cannot rename columns in Power Query whereas you can in Excel.
- The formula bar uses a programming language called M in Power Query, unlike in Excel.
- You cannot edit or format a cell in Power Query whereas you can in Excel.
- Power Query does not work at the cell level, whereas you can in Excel.
- Power Query treats empty cells as “null” whereas Excel treats these as a zero.
Q3. Alex was working in Power Query and loaded the data into a new worksheet. He notices that he has made an error and needs to undo one of his steps. What should he do?
- Go to Queries & Connections, choose the relevant query, click on Edit, go to Query Settings, and delete the relevant Applied Step
- Go to Queries & Connections, choose the relevant query, click on Edit, go to Query Settings, and click Undo.
- Click Undo.
Quiz 3: Get data from a database
Q1. When getting data from a database, unlike getting data from a spreadsheet you have to transform the data at the source first.
Q2. There are currently three columns in Power Query: Street Address, City, State, with data such as “42 Wallaby Way” (Street Address), “Sydney” (City), “NSW” (State).
What could we do to create a new column that displayed the full address as a single string, such as: “42 Wallaby Way, Sydney, NSW”?
- Select the relevant columns, click on Merge Columns and choose a custom separator “, “.
- Select the relevant columns, click on Merge Columns and choose Comma as the separator.
- It is not possible to merge more than two columns at a time in Power Query.
- Select the relevant columns, click on Merge Columns.
Q3. If you created a table in Excel after getting data from a database, changing the data in the new table will update the original database if you click Refresh.
Quiz 4: Get data from a folder
Q1. An American company has 50 offices, one in each state, who all use their own Excel spreadsheet for their human resources data, but the parent company wants to maintain a separate spreadsheet that gets the data from all these files. What would be an efficient solution to this problem?
- Get data from Folder.
- Get data from Text.
- Get data from Workbook.
- Get data from PDF.
Q2. When getting data from a folder, the preview panel only shows a preview of the first file. Suppose there is an Australian company with an office in each of the 8 states and territories, where the parent office is in the state of New South Wales. If the 8 files are named as below, which file will appear in the preview pane, and why:
- Western Australia
- South Australia
- Northern Territory
- Australian Capital Territory
- New South Wales
- New South Wales, because it is the parent office.
- Victoria, because it has the fewest characters.
- Australian Capital Territory, because it is alphabetically first.
- Western Australia because it is first in the list above.
Q3. Suppose that we have created a new table by getting data from a folder that contains data from each of 5 branches of a company, each with its own file. What should we do if we open two new branches – that maintains the existing structure and also gives unique information for each branch in its own file?
- Add two new files to the folder, one for each branch.
- Add one new file to the folder, that contains the data for the two new branches.
- Pick two files that already exist and add one new branch to each of these files.
Quiz 5: Append queries with 2 datasets
Q1. Which aspect of getting data from a folder is similar to the result of an Append Query?
- The data sets are merged side-by-side, like using a VLOOKUP function.
- The data sets are stacked on top of each other.
- The data sets are interspersed into each other.
- The result only includes duplicate entries.
Q2. When using an Append Query the two tables must:
- Have the same number of columns.
- Have a unique identifying field.
- Have columns that are common across the two tables.
- Have the columns in the same order.
Q3. In the video, we created 2 queries, one for Sydney and one for Other Instructors. We did not load these into the worksheet and we only created a connection. Due to this setup, when choosing to append these queries, the result cannot be loaded into the worksheet – we can only create a connection.
Quiz 6: Append queries with 3 or more datasets
Q1. When getting data from a PDF that contains a table in each of 5 pages, what will we see in the preview panel?
- Page001 to Page005
- Page001 only
- Table001 only
- Table001 to Table005
Q2. When getting data from a PDF that contains a table in each of 5 pages, and selecting multiple items – this will create as many queries as the number of items you have selected.
Q3. When getting data from a PDF that contains multiple pages, what would be a query that would be usually run, right at the end?
- A Concatenate Query.
- An Append Query.
- A Merge Query.
- A Combined Query.
Quiz 7: Merge queries — Left Outer join
Q1. A Merge Query performs a similar functionality compared to some Excel functions. Which functions are they?
- INDEX and MATCH
Q2. A course has two tables: Table 1 (on the left) which contains all the students who enrolled in the course at the beginning of the school year and includes students who have dropped out since January. Table 2 (on the right) contains all currently enrolled students in this course who sat for an exam.
What type of join do we need to figure out which students have dropped out of the course?
- Full Outer Join
- Right Anti Join
- Right Outer Join
- Left Outer Join
- Left Anti Join
- Inner Join
Q3. For a Merge Query to work we don’t need the column headings to match, however, we do need the values of one column in one query to relate to the values of a column in the other query.
Quiz 8: Merge queries — Inner and Anti joins
Q1. Suppose that we have two queries containing two employment data sets. Each data set has the historical employee details of two subsidiary companies of the same parent organisation. What type of join is required to find out the employees who have worked in both organisations?
- Left Anti Join
- Right Outer Join
- Right Anti Join
- Full Outer Join
- Left Outer Join
- Inner Join
Q2. What could you do if you needed to merge three queries?
- Run a merge query for two queries first and name this new query. Then run a merge query of this new query and your third query.
- Select all three queries from the dropdown menus within the Merge Query panel.
- This is not possible in Power Query.
Q3. If you have a query that displays the output from a Right Anti Join, the last few characters of M code in the formula bar will read JoinKind.RightAnti. What will happen if we edit the text in the formula bar to replace JoinKind.RightAnti with JoinKind.RightOuter and press Enter?
- The query will display the results of both the Right Anti Join and the Right Outer Join.
- You cannot edit the contents of the formula bar, you must perform the query using the menu options.
- Even though you can edit the formula in the formula bar, it will not have an impact on the query that you initially ran.
- The query will get updated and display the results of a Right Outer Join.
Week 2: Excel Power Tools for Data Analysis
Quiz 1: Applying transformations with the Query Editor
Q1. When in the Power Query editor, which of the following gives you access to the available tools to transform data?
- Right-clicking one of the column headings to get a contextual menu.
- Left-clicking any of the tools on the ribbon.
- Opening up Query Settings.
Q2. What does the step “Changed Type” mean under Query Settings?
- The case of the data has changed, such as upper-case characters becoming lower-case.
- Promoted the first row of our data into our column headings or column labels
- Changing the data type such as text data becoming numerical data.
Q3. The dropdown filter for a column in the Power Query editor works in a similar way to the dropdown filter for a column in Excel.
Q4. The keyboard shortcut CTRL + A performs the same task in Excel and the Power Query editor
Q5. Suppose you performed the following task in two steps:
Step 1 – Sort against one column.
Step 2 – Sort against another column.
- Excel does not retain the sort in Step 1, whereas the Power Query editor does.
- Excel retains the sort in Step 1, whereas the Power Query editor does not.
- Neither Excel nor the Power Query editor retains the sort in Step 1.
- Both Excel and the Power Query editor retain the sort in Step 1.
Quiz 2: Unpivoting data
Q1. Un-pivoting data is easy in the Query Editor but difficult with standard Excel tools.
Q2. Why would we usually select the columns we do not want to pivot and select Unpivot Other Columns?
- It is easier to undo the change if you make a mistake.
- The columns we do want to unpivot are the ones that are more likely to change.
- There may be a large number of columns that we want to unpivot.
Q3. What are some ways to re-use a query that has been used to unpivot a dataset, and apply the query to another similar dataset?
- Choose Data Source Settings in the new version of Excel.
- Change the source by modifying the M Code.
- Re-name the source step with the intended file name.
- Click on the cog to the right of Source in Query Settings.
Quiz 3: Pivoting data in the Query Editor
Q1. Pivoting data generally means restructuring and summarising data to produce a report.
Q2. When creating a regular PivotTable, Excel tries to aggregate the data, whereas when creating a pivot table with the Query Editor, we can choose to display the original data without aggregation.
Q3. In Pivot Column, under Advanced Options, Don’t Aggregate refers to returning the actual values.
Quiz 4: Grouping data
Q1. Which of the following options in Group By is not available in traditional Pivot Tables?
Q2. After performing a Group By, if we see a grouping occur two or more times, it is likely that one or more of these variables has a hidden character such as a space or another character.
Q3. When selecting Replace using special characters, some of the options include:
- Non-breaking Space
- Line Feed
Quiz 5: Working with columns
Q1. What is a delimiter?
- A character(s) that specifies the beginning of the string
- A character(s) that specifies the end of the string
- A character(s) that specifies the boundary between different parts of a string
Q2. Split Columns in Power Query has similar functionality to which tool in standard Excel?
- Text to Columns
- There is no similar functionality in standard Excel
- Unmerge Columns
Q3. Merge Queries are case sensitive.
Q4. When creating a custom column that contains a formula that adds up values from other columns, what would happen to the results of a row if one value was null.
- The answer will be N/A
- The answer will be #VALUE!
- Excel will treat the null as a zero.
- The answer will be null
Q5. Unlike standard Excel, when creating a custom column that is the result of a custom formula that contains an if statement, the if is followed by what other statements?
- then and else
- if.then and if.else
- No other statements follow an if in Power Query.
- and and or
Quiz 6: Excel Power Tools for Data Analysis
Q1. Power Query (Get and Transform) allows us to get data from a wide variety of sources, which of the following sources can we get data from using Power Query?
(Multiple answers may apply.)
- Text Files
Q2. Download the attached file and unzip it, then answer the questions below.
Open the Excel workbook Staff Expense Report and import the Staff table from the Staff.accdb access database file into the Staff sheet. What is the ID for Jane Freeman?
Q3. Have a look at the file Sydney Expenses. There are 3 worksheets, one for each of the staff you manage. You need to bring this data into your report. In Staff Expenses Report create a query to get the 3 sheets of data from Sydney Expenses. We are not ready to transform the data yet so save the three queries without loading the data into the spreadsheet. Which option did you select?
- Load To and then Table
- Load To and then Connection Only
Q4. Create a new query called Expenses that combines the data from the 3 queries you created previously, so the rows from each query sit one on top of the other ready to be transformed for analysis. Which combine query type do you need to use?
- Merge with a Left Outer Join
- Merge with Inner Join (matching items only)
Q5. In the Expenses query, column 4 contains the Staff ID for each expense sheet shown above the expense data. Transform the data so that the correct IDs repeat for every row of the expenses. Which option on the Transform tab achieves this?
- Fill Down
- Replace Values
- Fill Up
Q6. In the Expenses query have a look at the heading row, it does not contain the correct headings, the correct headings are in row 5. Which of the following sets of steps will get the values in row 5 to be our headers? (More than one option may apply.)
- Remove the top 4 Rows, then select Use First Row as Headers
- Select Use First Row as Headers, remove the top 5 Rows, then select Use First Row as Headers
- Remove the top 4 Rows, then select Use Headers as First Row
- Select Use Headers as First Row, remove the top 5 Rows, then select Use First Row as Headers
Q7. Still in the Expenses query, we need to remove the first and last columns. Select the first column, hold down the Ctrl key and select the last column. Which of the following options can you use to remove the selected columns? (More than one option may apply.)
- Press Delete on your keyboard
- Home Ribbon > Remove Columns
- Home Ribbon > Remove Other Columns
- Right Click and from the menu select Remove Other Columns
Q8. Some of the column headers do not have very meaningful names, change the column headings TRANSPORTATION to EXPENSE TYPE and 112 to STAFF ID. Which option on the ribbon allows us to change a column name?
- Replace Values
Q9. There are still some rows that contain heading and total information that need to be removed. Add a filter to the Code column to remove all rows that do not contain an Expense Code (scroll down to ensure you have them all). How many items did you need to filter out (unselect)?
Q10. We have one more transformation to make on our Expenses query, the amounts are currently shown in 3 separate columns (one for each month) which will make it difficult to do our analysis. We want to put all the amounts in a single column and have a separate column with the Month name in it. Start by selecting the first 3 columns (EXPENSE TYPE, CODE & STAFF ID). Which of the following options do you need to apply to achieve the required outcome?
- Unpivot Only Selected Columns
- Unpivot Columns
- Unpivot Other Columns
- Pivot Columns
Q11. Correct the column names, change Attribute to MONTH and Value to AMOUNT. This will require us to create another query step.
Q12. Close and Load your Expenses query to the Expenses sheet in your workbook. Create a PivotTable to show the total expenses by month. Which of the following is true?
- September had the highest while July had the lowest
- July had the highest while August had the lowest
- August had the highest while September had the lowest
- September had the highest while August had the lowest
Q13. We would like to create another PivotTable to show the breakdown by Expense Category, but we do not currently have Expense Category in our query. Have a look at the query list and you will see a query called Expense_Codes which gives us the Expense Code and Category. Modify the Expenses query so it includes another column that gets the matching Category for each Expense Code. Which combine query option did you need to use?
- Left Anti-Join
- Left Outer Join
- Full Outer Join
- Right Anti-Join
Q14. Create a PivotTable from the updated Expenses data that shows total expenses by Category. What was the total spent on Lodging and Meals?
Q15. Not all staff need their expenses approved. If we create a new query called Approval Required only containing staff who require approvals and then create a merge query with Expenses as our left (or top) table and Approval Required as our right (or bottom) table, which of the following merge queries will only return Expenses for staff requiring approval?
- Full Outer Join
- Inner Join
- Left Outer Join
- Left Anti-Join
Week 3: Excel Power Tools for Data Analysis
Quiz 1: Adding data to the Data Model
Q1. Which of the following tasks can be performed in Power Pivot?
- Filter the data
- Create a PivotTable
- Sort the data
- Edit the data
Q2. When importing data from a database such as Microsoft Access into the Data Model, we have the option to import tables or queries (subsets of the data).
Q3. The advantage of Power Pivot is that it can handle several million more rows than standard Excel. When CSV files are imported into Power Pivot, which of the following will be true of the Excel file size?
- The Excel file size will remain unchanged as the data is not actually imported into Excel.
- The Excel file size will be roughly the same as the original CSV.
- The Excel file size will be much larger than the original CSV.
- The Excel file size will increase but be significantly smaller than the original CSV.
Quiz 2: Data Modelling
Q1. Which of the following is the preferred relationship in a Power Pivot data model?
- All of the above
Q2. When creating a PivotTable by selecting fields from multiple sources, one must first establish the relationship/s between these tables. You can establish these:
- Creating the relationships manually when in Diagram View
- Re-naming the fields across tables to have consistent names
- Excel has an Auto-Detect feature
Q3. When in the Diagram view, it is essential to create the appropriate relationships between tables. Otherwise, creating a PivotTable by selecting fields across multiple tables will not produce meaningful results.
Quiz 3: Analysing data with Power Pivot
Q1. When creating a calculated column in Power Pivot, which of the following is true?
- We begin the DAX function with “=:” (an equal sign followed by a colon)
- The DAX functions apply to entire columns
- We use DAX functions instead of regular Excel functions.
- The syntax of a DAX function looks very similar to a structured reference in a table.
Q2. In the Active Field group, Expand / Collapse allows you to see the parent category and sub-categories in a hierarchy in a single view according to the level of expansion / collapsing. However, when Drilling Down / Drilling Up, you navigate to the desired level of the hierarchy without seeing the parent category.
Q3. Which of the following are features in Power Pivot that regular PivotTables do not have?
- The ability to show Subtotals.
- The ability to summarise the value field by Distinct Count.
- The ability to summarise the value field by Count.
- The ability to include filtered items in totals.
Quiz 4: Visualising data with Power Pivot and Cube Functions
Q1. Under the default settings, Power Pivot allows more chart types compared to standard Pivots.
Q2. We can access the data model from an Excel spreadsheet using regular Excel functions or Cube Functions.
Q3. Once we convert the data in a PivotTable into a formula using OLAP Tools, we can create a larger variety of chart types because the PivotTable ceases to be a PivotTable and is now a regular data set.
Quiz 5: Introduction to DAX measures
Q1. What are the features of a measure?
- A measure can have a set number format (currency or percent for example).
- A measure is a dynamic calculation.
- A measure’s result can change depending on the context (filters).
- A measure can perform a calculation using another measure.
Q2. There is no way of differentiating between explicit measures and implicit measures in the Field List.
Q3. Which of the following are DAX functions?
Quiz 6: Applying Time Intelligence and CALCULATE
Q1. Have a look at the available DAX Time Intelligence functions here: https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax
Which of these functions will return the last date of the month in the current context for the specified column of dates?
Q2. The CALCULATE function allows us to override the existing filter context.
Q3. The CALCULATE function can be combined with Time Intelligence functions to allow us to compare metrics for different time periods in the same PivotTable.
Quiz 7: Excel Power Tools for Data Analysis
Q1. Which of the following statements are true of Power Pivot? (More than one may apply, but please read carefully)
- Data stored in Power Pivot is highly compressed.
- Power Pivot allows you to store 10s or even 100s of millions of rows of data directly in an Excel workbook in a highly performant way.
- Power Pivot facilitates the creation of Pivot Tables that pull data from multiple unrelated data tables.
- Power Pivot introduces a powerful data analysis language called M.
Q2. Open the Excel file Company Expenses. In this workbook, we are going to use Power Pivot to analyse our company expenses for the year 2020. Start by adding the Cost Types data to the data model. On which ribbon tab do we find the option to Add to Data Model?
- Power Query
- Power Pivot
Q3. In the data model, you will notice other tables have already been added detailing the company expenses, the countries/regions where the data comes from and the business areas that generated these expenses. Have a look at the data in each table. Which of the tables are lookup tables? (More than one may apply)
- Cost Type
- Sales Region
- Business Area
Q4. In the data model change to the diagram view and reorganise the tables so that the lookup tables are all above the data table(s). Some of the relationships have already been added. Which statement is true of the existing relationships?
- They are a mix of one-to-many and one-to-one
- They are all one-to-one
- They are all many-to-many
- They are all one-to-many
Q5. There is already a Calendar table in the data model, but if there wasn’t, we could create one by selecting Date Table (on the Design Ribbon) and then New.
Q6. The Cost_Type table does not yet have a relationship which means we will not be able to use it for filtering our Pivot tables. Create a relationship between Cost_Type and the appropriate table. Which table should Cost_Type be related to?
Q7. Using the data in the data model, create a PivotTable in the Monthly Costs sheet. Drag Amount (from Expense) to the Values section and Month (from Calendar) to the Rows section to show the total expenses by Month. Format the values to be Currency. Which month had the lowest expenses?
Q8. In the PivotTable we just created, the Sum of Amount is an example of an Explicit Measure.
Q9. DAX or Data Analysis Expressions are functions that can be used to extend our data model. Which of the following are true of DAX functions? (More than one may apply)
- DAX functions can be used to create calculated columns.
- DAX functions work with columns and tables rather than individual cells.
- DAX is a formula language and has many functions very similar to Excel Functions.
- DAX functions can be used to create calculated measures.
Q10. DAX measures allow us to use a much greater range of calculations in our pivots. Create a DAX measure called Median Expense to calculate the median expense amount, format it to currency. Add Median Expense to the Pivot Table you created previously. What was the median expense for June?
Q11. Create another PivotTable in the Region Expenses sheet. Add Sales Region and Country to the Rows area and Amount to the Values area. Which country in Europe had the second-highest expenses?
Q12. Click on one of the Sales Regions in the PivotTable. On the Pivot Table Analyse Ribbon note that the Drill Up/Down tools are greyed out. What do we need to do to make these options available?
- Create a Hierarchy
- Create an Explicit Measure
- Create an Implicit Measure
- Create a Cube Function
Q13. We would like to represent the data shown in the new PivotTable using a Treemap chart, but Pivot charts do not support Treemaps. One way of achieving this is to convert our PivotTable to…?
- DAX functions
- Cube functions
- COUNTIFS functions
- SUMIFS functions
Q14. Consult the DAX Function Reference. CLOSINGBALANCEMONTH and OPENINGBALANCEMONTH are examples of which type of DAX function?
- Information functions
- Time intelligence functions
- Date and time functions
- Financial functions
Q15. Which DAX function performs a calculation that overrides the PivotTable filter context.
Week 4: Excel Power Tools for Data Analysis
Quiz 1: Introducing Power BI
Q1. Which of the following are views in Power BI?
- Transform View
- Report View
- Model View
- Data View
Q2. From which of the following sources can we import data into Power BI? (Multiple answers may apply.)
- SQL Server
Q3. Power BI will automatically create the relationship between tables if field names match across tables (with a few extra checks).
Quiz 2: Adding a Calendar using DAX
Q1. Which of the following would help us create a calendar table in Power BI?
- Calendar tables are automatically added by Power BI.
- Create a calendar table using DAX in Power BI.
- Import a calendar table using Power Query
- Generate a calendar table using the M language in Power Query
Q2. What is the purpose of the Mark as date table button?
- There must be one date table in any data model.
- Otherwise, Power BI will automatically designate a date table from your data model.
- Power BI then formats the data as dates.
- Power BI then knows to use this table for any time intelligence functionality
Q3. Which of the following DAX functions returns a table with a single column named Date that contains a contiguous set of dates?
Q4. If you click on a blank area of the canvas before clicking on a visualisation, Power BI will add the visualisation in this blank area.
Quiz 3: Creating a Power BI report
Q1. In Power BI, when we change the theme we change the colour scheme for which of the following?
- The selected series/category
- The current page
- The current report
- The selected visualisation
Q2. When you click on New measure, the formula bar is automatically populated with “Measure = “. Here, the word on the left-hand side of the equation is the name of the measure, which is a name you can choose.
Q3. Sometimes a single number is the most important information you need to track in your Power BI report. Which visualisation is best suited to showing a single numeric value?
Q4. Which of the following are displayed when you hover over a violin plot? (More than one may apply.)
Quiz 4: Working with reports
Q1. When you click on Show as a table on any visualisation in the report, what happens?
- The data is exported to a CSV file.
- The visualisation is replaced by a table of the data.
- The data is exported to Excel.
- The data is shown in a table below (or next to) the visualisation.
Q2. Fill in the blanks:
is a data analysis language. is a transformational language.
- DAX; M.
- M; DAX.
- M; M.
- DAX; DAX.
Q3. What does dragging and dropping one field onto another do in the Fields pane?
- The formatting of the dragged field is copied over the formatting of the field it is dropped over.
- It creates a hierarchy.
- It concatenates the two fields.
- The dragged field replaces the one that it is dropped over.
Q4. Unlike Excel pivot tables, filters in Power BI allow us to filter all the visualisations on the page or on all pages in the report.
Q5. By default, slicers in Power BI apply to all pages in the report.
Q6. If you hover over a filter icon on any of the visualisations in a report, it will let you know all the filters that have been applied.
Quiz 5: Adding time intelligence
Q1. Which of the following are valid time intelligence functions in DAX?
Q2. When using time intelligence DAX functions to create a visualisation that shows increases and decreases over time, which of the following visualisations would be most effective?
- A pie chart.
- A treemap chart.
- A violin chart.
- A waterfall chart.
Q3. What is the advantage of the DIVIDE function in DAX compared to using the standard divide numerical operator?
- It allows us to specify the number of decimal places returned.
- It is more accurate.
- There is no advantage, both perform equally well.
- It allows us to handle a divide by zero error.
Q4. Which of the following are valid icon sets in Power BI? (More than one may apply.)