Power BI quiz – BIDataLab

BIDataLab Power BI quiz

Your Name

Your Email

1. What is the next Power Query step you should take?

1. When loading data into Power BI, you come to the step like the one presented in the following figure:

You want to create a single Year column to be able to slice data by year.

Press Close & Apply.

Right click the Country column, go to the Transform tab and click “Pivot column”.

Merge Country and State columns then press Close & Apply.

Select Country and State columns, right-click and select Unpivot other columns

2. How do you do that?

2. You have 5 identical queries that represent the Sales data for 5 years as shown in the screenshot below. You want to compare Sales Year over Year and want to see the difference between the same month in each year.

Click Close and Apply and then create necessary measures using DAX

Combine all these Queries into single Append Query

Combine all these Queries into single Merge Query

Use “Merge Queries as New” to keep original queries

3. What is the next Power Query step you should take to resolve the errors?

3. While loading data from an excel file, you get 5 errors, and when you click at each error cell, you notice that all errors are DataFormat.Error:
Invalid Cell Value ‘#N/A’ as shown in the screenshot below.

Select Quantity and Total Sales Amount columns, click Remove Rows and then click Remove Errors

Select Quantity and Total Sales Amount columns, click Keep Rows and then click Keep Errors

Select Quantity and Total Sales Amount columns, click Replace Values, then on the “Value to Find” type #N/A and on the “Replace With” type 0

Select Quantity and Total Sales Amount columns, click Replace Errors, then on the “Value” type null

4. What do you do next in order to connect each quarter to its appropriate Year value?

4. You have the following Sales data in an Excel sheet:

You load this data into the Power Query Editor and get to the step shown below:

You notice that the Year Value 2019 is read only for the first quarter of that year, whereas for other quarters the year value is null. The same thing happened for the Year 2020 (not shown in the screenshot).

Select Column 1, click Transpose, then select Column 1 and click Fill > Down

Apply the “Use First Row as Headers” step, then rename Column2, Column3 and Column4 as 2019. Repeat the process for 2020.

Merge the four columns for each Year

Replace ‘null’ values with 2019

5. What do you need to do to create that?

5. The dataset below shows Sales by City for the stores your company owns. Your manager wants to see a pie chart that groups sales by Coast (West Coast vs East Coast).

Load the data and use DAX to create a Calculated Column named Coast

Add a Custom Column called Coast in the Power Query Editor and write the code using M Language

Add a Coast column using the Add Conditional Column dialog box

Modify the data source by adding the Location column to a new Coast column

6. How do you achieve this?

6. You loaded some Sales data into Power BI and placed two columns in a Table visual as shown below. You notice that the Total Sale Amount column does not combine all the data for each location. You want to get only 1 row per location.

Select the Total Sale Amount column and choose the Count as Summarization option

Go to the Power Query Editor and change the Total Sale Amount column data type to Whole Number

Go to Power Query Editor and change the Total Sale Amount column data type to Fixed Decimal Number

Click Refresh and the data type will be automatically fixed.

7. How do you proceed to accomplish the request of the Sales Department Head?

7. Your Sales Department Head asked you to prepare a report from income statements form 2018, 2019, and 2020 which are saved as excel files. The Sales Department Head also wants to be able to add new files for subsequent years without having to contact you. You plan to load the relevant excel files from a folder as shown below.

When you investigate the excel files, however, you notice that the data needs to be cleaned before it can be combined. Below is an example of what you see in the 2018 file.

Go to Transform data, clean each dataset then Combine them within Power Query

Go to Transform data, clean the data in the first file and then create a function that will be used for every file within the folder.

Click Combine and Transform data, then clean the combined dataset

Click Combine & Load to ensure that every new file will be loaded automatically

8. What is causing this error?

9. You are preparing a Power BI report that uses three different excel files. You upload the files and combine them, when you see the error report shown below.

The files names are different

The location of the files is different

The sheet names within the excel files are different

Column Headers within sheets in Excel files are different

9. How do you ensure that data from all sheets are automatically read and combined into the Power Query Editor?

10. Your company manager has given you a task to load Sales data from ten years into Power BI to create some reports. The data is stored in one excel file using a different sheet for each year as shown below.

Create 10 queries, one for each sheet, then combine them using Append Queries option

Create 10 excel files, each containing data for one year, put them all into a folder, then load them into Power BI using Get data from Folder connector

Load data from each sheet separately, then do calculations using DAX formulas

In the Data Navigation Step, use Parameter as the sheet name, thencreate and invoke a custom function that will be applied to all the sheets

10. Which tool do you need to use?

8. You are preparing a Power BI report that uses the Income Statement data for 2018, 2019, and 2020 as shown below.. The three files are in Excel format, and you want to create a single dataset from these files.

Combine files

Merge Queries

Append Queries

Group By

11. How do you resolve this issue?

11. You are loading an excel file which has statistics data for different car manufacturers. In the process of transforming the data in the Power Query Editor, you get the Data Format Error shown below.

Remove the row that contains the error

Replace the error with a N/A value

Replace the error with “null.”

Replace the error with 0

12. Which Power Query steps do you need to take to answer the manager’s question?

12. Your company created a survey where the respondents need to answer the question: “Which are your favorite fruits?” You get the responses in the format shown below where the left column shows the number assigned to each respondent (R1, R2, etc) and the right column the answers each respondent gave.Your manager wants to know which fruit was mentioned the most and which the fewest times..

Split the Answers column by comma delimiter and then Pivot the Respondent column

Split the Answers column by comma delimiter, then select the Respondent column and Unpivot Other Columns

Create 3 new columns using the Column From Examples option, and then Pivot Question column

Create 3 new columns using the Column From Examples option, and then Unpivot Other Columns

13. What is the first Power Query step you need to take to achieve the desired output?

13. Your manager asked you to calculate the change in sales from 2019 to 2020 for each of the company’s products using the dataset shown below.

Pivot the Product Category column and use the Metric column for the Values input.

Pivot the Product Category column and use the Amount column for the Values input

Pivot the Metric column and use the Product Category column for the Values input

Pivot the Metric column and use the Amount column for the Values input

14. What is the first step you need to take to do that?

14. You work for a company that sells vehicles and vehicle parts. You want to create a pie chart that shows the sales of all vehicles as a group and the sales of all vehicle parts as a second group for the year 2019, using the dataset shown below.

Add new conditional column

Add new custom column

Invoke Custom Function

Add Index column starting from 1

15. Which tool do you need to use to normalize the spelling of all entries?

15. You have a column with State names. The column contains duplicates of state names due to errors in capitalization, as shown in the image below.

Replace values

Extract

Format

Fill

16. How do you do that in Power BI once you load the data?

16. You have a dataset that with measurement data from different machinery over a period of 5 days as shown below. You want to compute the average measured value per day.

Add Custom column: Average = List.Average ([Day 1],[Day 2],[Day 3],[Day 4],[Day 5])

Add Conditional column: Average = ([Day 1]+[Day 2]+[Day 3]+[Day 4]+[Day 5])/5

Select 5 Day columns then choose Standard → Average

Select 5 Day columns then choose Statistics → Average

17. How do you fix this error?

17. You loaded some sales data into Power BI that, among other columns, contains a Sell Date column. After using a Change Type step in Power Query, you get the Data Format Error shown below.

Delete the previous Changed Type step and add a new step Change type by locale

Replace Errors with “null”

Remove rows that contain errors

Replace errors with “null” and use the Fill down tool to get approximate Sell date

18. How do you achieve that?

18. You have the dataset shown below about the construction of some buildings. You want to create a visualization that shows the count of buildings by Period and want to ensure that the periods are shown in correct chronological order:

* <1970
* from 1970 - 2000
* > 2000

Create a new conditional column and assign “1” to “2000”, “2” to the middle period, and “3” for the latest chronological period

Load the data as is and then sort it within the visualization itself

Select the Period column and sort it by Year Built Ascending

Select the Period column and sort it by Year Built Descending

19. How do you create a column that will display the date in the format appropriate for your needs?

19. You are preparing a report that will show orders by date using the dataset below.

Click Extract > Text After Delimiter “-” and change the type of the new column to date

Click Extract > Text After Delimiter“ “ (blank space) and change the type of the new column to date

Click Extract > Text Before Delimiter “ “ (blank space) and change the type of the new column to date

Click Extract > Text Before Delimiter “-” and change the type of the new column to date

20. How can you do that?

20. You are working on a dataset that has a “Order Total” column as shown in the image below. You want to change the column data type to Fixed Decimal Number.

Replace the $ sign with nothing, and then change the data type

Split the column by delimiter

Change the data type directly

Replace “ . “ with “ ,” and then change the data type

21. What caused this error?

21. You have created a report that uses data from an excel file that is stored in your PC. Your manager asked for the pbix file, and when he opened the file and tried to refresh, he saw the error message shown below.

A pbix file works only on the PC on which it was created

While trying to refresh, Power Query searched for the path in the manager’s PC and couldn’t find the excel source file

The name of the source Excel file has changed

The pbix file name has changed

22. You want to create a Location dimension table with only one column Location. What are the Power Query steps you should take to create that table?

22. You have a dataset that has sales data and one of the columns is called Location ,shown in the image below.

Duplicate the column Location then Remove Duplicates

Add as New Query from the Context menu and then Remove Duplicates

Add a Custom Column and then Remove Duplicates

Add a Custom Table and then Remove Duplicates

23. What is the easiest way to create this?

23. You have the list of employee names shown in the screenshot below. You want to create another column with the email of each employee. You know that the email structure for all employees is firstname.lastname@mycoompany.com.

Click Split Column>By Delimiter >Space, then merge the columns and add additional data

Add custom column

Add conditional column

Add column from examples

24. What should you do in this situation?

24. You are trying to load data into Power BI Desktop, when you get the error message shown in the image below.

Click Close, fix the errors in the source file, and then reload.

Click View Errors, fix errors in the Error query, and reload

Click View Errors, fix errors in the original (2019) query, and reload

Click close, and investigate the errors in the Table View

25. What do you need to do in Power Query to properly transform this dataset?

25. You loaded some order data from your company into Power BI Desktop and got the table shown below

Transpose table

Use First Row as Headers, then Transpose table

Use First Row as Headers, then Unpivot

Demote headers, then Transpose table

26. How do you create this column?

26. Your company Orders data has a Price (M) column that holds data in millions as shown in the image below. You want to create a new column that shows the full Price value, not in millions.

Add Column> Standard>Multiply by 1000

Add Column> Standard> Divide by 1000

Add Column> Standard> Divide by 1000000

Add Column> Standard> Multiply by 1000000

27. How do you create a visual that shows Sales and Expenses over time in a single Line Chart?

27. You load 2 tables into a model, one that stores Sales over time, and the other one that stores Expenses over time as shown in the screenshots below.

Put the Date column from the Expenses table in x-axis.

Create a relationship between the two tables using Date column and put either date column as a Slicer

Create a separate Date table with ‘one-to-many’ relationships to the existing tables, and use the date column from the Date table as the x-axis of the line chart

Create a relationship between the existing tables using the ID columns and use Date column from Expenses table as x-axis in the line chart

28. Which of the following columns is a dimension column?

28. Your model has a table with multiple columns and some of them are shown in the screenshot below.

Units Sold

Manufacturing Price

Sales Price

Profit

29. Which of the following statements is true?

29. You have a data model which is shown in the screenshot below. The model has 5 tables.

Tables Countries and Segments cannot be dimension tables, because they contain only one column

The model can be improved if we turn on bidirectional filtering in all relationships.

Dates table is a Fact table that contains Date related columns

Financials table is a fact table which can be filtered using columns in other dimension tables

30. 30. Which of the following statements is NOT true about Fact tables?

The fact table typically is the largest one in the model

There can be only one fact table in the model

The model may contain only one table

Measures are typically aggregation of numbers from the fact table

31. 31. Which of the following statements is true about Dimension tables

You can create measures using columns in a dimension table

A Dimension table must always contain only one column

A Dimension table cannot be related to another dimension table

Dimension tables must be created using Power Query

32. Which of the following statements is NOT true about this model?

32. You have a data model shown in the screenshot below.

This model represents a typical Star Schema

In this model there are 4 dimension tables and 1 fact table

We cannot add any other fact tables in this model

We can add other dimension tables in this model

33. What is the meaning of these icons?

33. From the star schema model presented in the following screenshot, we can notice that some columns have a different icon compared to other columns.

These columns are hidden in the Report view

These columns are hidden in the Data view

These columns are deleted from the fact table, because we already have them in the dimension tables

These columns are deleted to make the model smaller and to increase the performance

34. Which of the statements is correct for this model?

34. You have a model with three tables, as it is shown in the screenshot below.

This model represents a typical Star Schema

In this model there are 2 dimension tables and 1 fact table

This model represents a typical Snowflake Schema

In this model there are 2 fact tables and 1 dimension table

35. 35. Which of the following statements is NOT true regarding the Date Table

Date table can be created using CALENDARAUTO dax function

Date table must have at least one Date column

There can be only one Date table in the model

Date table must have one row per day

36. How do you do that?

36. In the following screenshot we can see Sales my Month. Your manager asked you to sort the values by month so January comes first, then February and so on.

Sort Month column by Month No column

Sort Sales column by Month No column

Sort Month column Ascending

Sort Month column Descending

37. Which of the statements is NOT correct regarding this model?

37. You have a model with two tables like it is shown from the screenshot below.

Both tables are fact tables and the they should be related by Order ID column

We can create a dimension table called Location, which will use some of the columns in Orders table

Because both of these tables are Fact tables, we need to set cross filter direction to “Both”

Most likely there are more rows in Orders_Product table, than in Orders table

38. Which of the following statements is NOT true regarding this relationship?

38. In the data model below, there are four one-to-many relationships depicted with a full line, and there is a relationship depicted with a dotted line.

The dotted line tells the relationship is inactive

The dotted line tells the relationship is not needed

You can make the relationship presented by a dotted line active if you first deactivate existing active relationship

This relationship can be activated by a DAX function when creating a measure

39. What does Cardinality Many to One means?

39. In the screenshot below, we can see that there is an active relationship between the financials table and Dates table.

One or Many rows in Financials table relates to One row in Dates table

One or Many rows in Financials table relates to One or Many rows in Dates table

One row in Financials table relates to One row in Dates table

One row in Financials table relates to One or Many rows in Dates table

40. Which of the following DAX functions activates an inactive relationship?

40. You have a data model like in the screenshot below. You need to activate the inactive relationship in order to calculate Total Products sold and the slice that value by month in a line chart.

RELATEDTABLE

LOOKUPVALUE

USERELATIONSHIP

41. How do you create this relationship?

41. You model consists of two tables, one Sales table and one Purchases table as it is shown in the screenshot below. You want to create a relationship between these two tables.

Relate Order_ID with PO_ID in a One-to-One relationship

Create Date dimension table and then relate it to existing tables using date columns

Relate Order date with Purchase Date in a Many-to-Many relationship

Use UNION function to combine data from both tables into a single one

42. What do you do in this situation?

43. You are working as a Power BI developer in a company that has a lot of Sales data. You created a star schema model the managers can see sales by year, month and day. One of your managers asks you to add a chart where she can see Sales by Week.

Create a separate Week table which holds week numbers 1 to 52 per each year

Create a Week number column in Financials table

Create a Week number column in Dates dimension using WEEKDAY function

Create a Week number column in Dates dimension using WEEKNUM function

43. How do you create this functionality?

44. You are working in a simple data model consisting of a Sales Fact table and a Dates dimension table. Your manager asked you to exclude from calculations any day which is a Holiday in which your stores are closed.

Add a new table which will have at least one column of datatype date, where you put holidays dates, then relate it to the Dates table

Remove Holiday dates from the existing Dates table

Filter out all Holiday dates using Filters pane

Modify your measures to not include Holiday dates

44. How do you implement this?

45. Your model has a Sales table and a Locations table which has 8 rows as it is shown in the screenshot below. The manager can see Sales by Location, but now she wants you to create reports where each location user can see only their data and not the data that belong to other locations.

Duplicate existing report eight times, then enter Power Query and filter only one Location per file

Create different measures for each location

Create Location Roles in Power BI Desktop

Split the data source into 8 parts, one per each Location

45. Which table filter DAX expression must be written in the Manage Roles window?

46. You want to create a role for the users that come from Oregon.

[Location] = "Oregon"

[Location] = Oregon

[Location] = ‘Oregon’

[Location] = [Oregon]

46. Your model has two tables as it is shown in the screenshot below. You want to create an Items dimension table that will have one column with all unique items description from both tables. Which dax functions combination you use to achieve this?42.

CALCULATE and DISTINCTCOUNT

CALCULATE and UNION

UNION and DISTINCT

UNION and DISTINCTCOUNT

47. Which DAX function is used to create a What-If parameter in Power BI Desktop?

47. Your manager asked you to create a What-if analysis to analyze the sales performance based on a parameter.

GENERATESERIES

GENERATE

GENERATEALL

48. What else do you have to do in order to use this parameter in your analysis?

48. When you create a What-If parameter, Power BI creates a table with the possible parameter values.

Create a relationship between the Parameter table and the main Fact table in you model

Use the Parameter column value when you create measures

Use the Parameter Value measure when you create measures

Treat Parameter table as a dimension table

49. Which of the following statements is NOT true regarding relationships in Power BI Desktop?

49. When you load tables into Power BI Desktop, some of the relationships are automatically created.

You can use Autodetect... option to try and find relationships among tables in your model

You can create new relationships whenever needed without using Autodetect... option

The model will break if you delete the relationship between fact table and the date dimension table

Many-to-Many relationships are supported in Power BI Desktop

50. What is the cause of this strange behavior?

50. You model consists of two tables: Sales and Location tables. You create a bar chart which shows Sales by Location using Sales amount column in Sales table and Location column in Location table. The result seems incorrect because there is the same sales amount for each location.

There is no problem here, all location had same sale amount

There is a missing relationship between the two tables in the model

The visual need to be sorted by Location

There are other locations missing in the Location table

51. What schema is this?

51. You have a model with one fact table and three dimension tables like in the screenshot below.

Star Schema

Diamond Schema

Line Schema

Snowflake Schema

52. What do you do to achieve the desired output?

52. You have a model like in the screenshot presented below. Your manager wants to know now many unique products are sold per Day.

Turn cross filter direction to Both in Product - Financials relationship

Turn cross filter direction to Both in Date - Financials relationship

Create a direct One-to-One relationship between Product and Dates tables

Create a direct Many-to-Many relationship between Product and Dates tables

53. Which of the following formulas calculate that?

53. You have the dataset shown below that’s named Financials. The Sales manager of your company wants to know how many Units of your company’s products are sold in Europe in total.

Units Sold Europe = CALCULATE (SUM (Financials[Units Sold]), Financials[Country] = "Europe")

Units Sold Europe = CALCULATE (SUM (Financials[Units Sold]), Financials[Country] = "Germany") + CALCULATE (SUM (Financials[Units Sold]), Financials[Country] "Mexico")

Units Sold Europe = CALCULATE ( SUM (Financials[Units Sold] ), Financials[Country] = "Germany" || Financials[Country] = "France"

Units Sold Europe = CALCULATE ( SUM (Financials[Units Sold] ), Financials[Country] = "Germany", Financials[Country] = "France" )

54. What is the correct rule that will achieve this functionality?

54. You have created a visual that shows Profit by Segment and you present this visual to your manager. She notices that the Enterprise Segment has negative value, she asks you to format this visual so that any column with negative value appears in red.

You select the visual, go to the Format pane and do to select→ Data colors. , then uUnder the default color you choose to conditionally format the negative value.

Format by Color Scale, based on Sum of Profit field, Minimum Value: Red and Maximum Value: Blue

Format by Color Scale, based on Count of Segment field, Minimum Value: Red and Maximum Value: Blue

Format by Rules, based on Sum of Profit field: If value is less than 0 then “Red” If value is greater or equal to 0 then “Blue”

Format by Rules, based on Sum of Profit field: If value is greater than or equal to -100000000 and is less than 0 then “Red” If value is greater than or equal to 0 and is less than 100000000 then “Blue”

55 out of 54

Time is Up!