PL-300: Microsoft Power BI Data Analyst
During your data QA process, you notice that there are null values in the Return type column (Returned, No return, and null). After talking with your manager, you decide to impute (replace) the null values with a value of “No return”. Which of the following actions should you take?
Select the Return type column > Transform > Replace values
Select the Return type column > Transform > Fill down
Select the Return type column > Add column > Column from example
Select the Return type column > Home > Remove rows
Answer is Select the Return type column > Transform > Replace values
You manage a Power BI model that has two tables named Sales and Product.
You need to ensure that a sales team can view only data that has a CountryRegionName value of Unites States and a ProductCategory value of Clothing.
What should you do from Power BI Desktop?
Add the following filters to a report. CountryRegionName is United States ProductCategory is Clothing
From Power BI Desktop, create a new role that has the following filters. [CountryRegionName] = "United States" [ProductCategory] = "Clothing"
Add the following filters in Query Editor. CountryRegionName is United States ProductCategory is Clothing
From Power BI Desktop, create a new role that has the following filter. [CountryRegionName] = "United States" && [ProductCategory] = "Clothing"
Answer is From Power BI Desktop, create a new role that has the following filters. [CountryRegionName] = "United States" [ProductCategory] = "Clothing"
First, You have 2 tables (Sales & Products),
Second, You have to use RLS to prevent a sales team to view some data,
Third, You have to create 2 filters, one for Sales(CountryRegionName) & one for Product(ProductCategory) as there are 2 different tables, so there are 2 filters NOT just one filter.
Note: you can't filter 2 columns from 2 DIFFERENT TABLES using && but you can use && in case of the columns you wanna filter in the SAME TABLE only, so D in incorrect.
You have a table named Sales that contains sales data for the United States. A sample of the data in Sales is shown in the following table.
When you attempt to create a map that shows SalesAmount by Zone, you discover that the map shows a bubble based on cities instead of states.
You need to ensure that the map shows bubbles based on states.
What should you do?
Add a column named Country that contains United States as the value.
Add a column for longitude and a column for latitude.
Select the Zone field. From the Modeling tab, change the Data Category.
Select the Zone field. From the Modeling tab, change the Data Type.
Answer is Select the Zone field. From the Modeling tab, change the Data Category.
This is a common problem when the data in your location field is ambiguous, such as using an area name like Washington, which could indicate a state or a district.
One way to resolve the location data problem is to rename your column to be more specific, such as State. Another way is to manually reset the data category by selecting Data Category on the Modeling tab. From the Data Category list, you can assign a category to your data such as "State" or "City."
References:
https://docs.microsoft.com/en-us/power-bi/guided-learning/visualizations#step-5
You have a query that retrieves data from a Microsoft Azure SQL database.
You discover that a column named ErrorCode has several values starting with a space character, and a column named SubStatus contains several non-printable characters.
You need to remove all the leading whitespaces from ErrorCode and all the non-printable characters from SubStatus. All other data must be retained.
What should you do on each column?
Answer is Trim and Clean
Reference:
https://docs.microsoft.com/en-us/powerquery-m/text-trim
https://docs.microsoft.com/en-us/powerquery-m/text-clean
From Power BI Desktop, you create a query that imports the following table.
You need to configure the table to appear as shown in the following table:
What should you do?
From the Format menu, click Trim.
From the Extract menu, click Last Characters.
From the Split Column menu, click By Delimiter.
From the Extract menu, click Text After Delimiter.
Answer is From the Extract menu, click Text After Delimiter.
You need to end with one column. You need to remove the part you are not interested in. The "Text Delimited" option in the Extract Menu
You are importing sales data from a Microsoft Excel file named Sales.xlsx into Power BI Desktop.
You need to create a bar chart showing the total sales amount by region.
When you create the bar chart, the regions appear as expected, but the sales amount value displays the count of sales amount instead of the sum of sales amount each region.
You need to modify the query to ensure that the data appears correctly.
What should you do?
Delete the query, import the data into Microsoft SQL Server, and then import the data from SQL Server.
In Query Editor, add a calculated column that totals the sales amount column.
Change the Data Type of sales amount column to Numeric.
Refresh the data model.
Answer is Change the Data Type of sales amount column to Numeric.
There is no Numeric data type in Power BI.
You have a Microsoft SQL Server Analysis Services (SSAS) cube that contains historical data.
In Power BI Desktop, you have the following query for the cube.
The query retrieves 25,499 records.
When you check the data warehouse that is the source of the cube, you discover that there are 26,423 records.
You need to ensure that the query retrieves all 26,423 records.
What should you do?
From Query Editor, refresh all the data.
Change the query to use Live connection mode.
Delete the Remove Duplicates step.
Add an Unpivot Columns step.
Answer is Delete the Remove Duplicates step.
Remove Duplicates step is added from FACT internet sales 'CarrierTrackingNumber' column
You plan to use Power BI Desktop to import 100 CSV files.
The files contain data from different stores. The files have the same structure and are stored in a network share.
You need to import the CSV files into one table. The solution must minimize administrative effort.
What should you do?
Add a folder data source and use the Combine Files command.
Add a folder data source and use the Merge Queries command.
Add a Microsoft Excel data source and use the Merge Queries command.
Add text/CSV data sources and use the Append Queries command.
Answer is Add a folder data source and use the Combine Files command.
Combine: It only appears in the case of the 'Folder' data source used to combine multiple files at one go
Append: Used when data already reside in Power query editor
Merge: It's simply a join operation on tables resides in Power query editor
References:
https://docs.microsoft.com/en-us/power-bi/desktop-combine-binaries
You have the following two queries in Power BI Desktop:
- A query named Query1 that retrieves a table named SMB_Customers from a Microsoft SQL Server database
- A query named Query2 that retrieves a table named Enterprise_Customers from an Oracle Server database
Both tables have the same columns.
You need to combine the data from SMB_Customers and Enterprise_Customers.
Which command should you use?
Combine Files
Merge Queries
Merge Columns
Append Queries
Answer is Append Queries
Append is used for same structure table, combine vertically.
Merge is used for kinda like Vlookup function(via common column), combine horizontally to pull out the other table's some column's info, also could think of the RELATED function in DAX.
References:
http://radacad.com/append-vs-merge-in-power-bi-and-power-query
You are previewing an Excel file in Power Query Editor in Power BI Desktop. The file contains data in a matrix format with years on the columns, months on the rows, and sales amount in the values.
You need to shape the data into a table with three columns.
What should you do?
Unpivot
Pivot
Use First Row as Headers
Transpose
Answer is Unpivot.