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 None 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 None 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 None 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 None 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 None 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. None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 None 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 RELATED USERELATIONSHIP None 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 None 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 None 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 None 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 None 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] None 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 None 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 IF None 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 None 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 None 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 None 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 None 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 None 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" ) None 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” None 1 out of 54 Time's upTime is Up!
