PL-300: Microsoft Power BI Data Analyst part 1

many-to-many relationship

What is it called when multiple records in one table are associated with multiple records in another table? many-to-many relationship one-to-many relationship many-to-one relationship

Abbreviated column names

What is not a best practice for naming conventions in Power BI? Rename columns to have spaces in them Replace values that have integers with human readable results Abbreviated column names

Quantity Sold Data tables contain measurable metrics about the business, like quantity sold. Data tables contain amounts, changes, decreases, increases, etc.

Which of the following fields would typically find in a data table? Product name Retail price Quantity Sold Product Brand

All of the above Power BI can connect to virtually any type of data source, including (but not limited to) SQL databases, R scripts, and Google Analytics.

Which of the following sources can Power BI connect to? SQL Database Google Analytics R scripts All of the above

SharePoint Online SharePoint Online organizes information into sites, document libraries, folders, and files within folders.

Which of the following sources contains sites, document libraries, and folders? SharePoint Online Microsoft Dataverse Power BI Libraries Filing Cabinets

PBIDS Files .pbid files are Power BI Desktop files that contain data, data source information and connection settings allowing users to easily connect and speed up the "Get Data" process.

Which of the following sources lets users connect to a set of pre-wired connections? PBIDS Files JSON Files Dataflows SSAS Tabular

Appending queries Append to stack, merge is side by side.

Which of these operations adds rows to an existing table? Group By Pivoting Merging queries Appending queries

An app

A collection of ready-made visuals, pre-arranged in dashboards and reports is called what? The canvas An app A dataset Scheduled refresh

Role-playing dimension "filter related facts differently..."

A dimension that can filter related facts differently is called what? Role-playing dimension Snowflake dimension Degenerate dimension

Studying the nuances of the data

Data profiling is defined as what? Aggregating columns containing numeric data Studying the nuances of the data Data modeling

To change data source values dynamically Parameters are a useful way to change data source values dynamically in Power Query. These parameters are different from the "What-If" parameters created in the Power BI Desktip front-end.

How can you use parameters when connecting to data? To connect to a JSON file To change data source values dynamically To create "What-If" scenarios To shape and transform data in the Query Editor

1,000

How many rows does Power Query scan to detect the type of data in the columns? 10,000 1,000 100

Append

If you have two queries that contain different data with the same structure, and you want to combine them into one query, which operation should you perform? Merge Append Combine column

Open the Data Source Settings dialog box and locate the data source. Select Edit Permissions and enter new credentials. Simple data source settings, locate source, edit permissions.

In Power BI Desktop, you are updating an existing report that connects to a Microsoft SQL Server database. You use database (SQL authentication) credentials to connect to the source. The last credentials that were entered are expired. You need to update the credentials. Which two actions should you perform? Open the Data Source Settings dialog box and locate the data source. Select Edit Permissions and enter new credentials. Open the Get Data dialog box and make a new connection to the database. Open the Options dialog box and allow data previews to download in the background.

Foreign key Cardinality refers to the uniqueness of values in a column, and whenever there is only one instance of each value, then it is a primary key, if there are many instances then it is a foreign key.

In a one-to-many relationship cardinality, what is the "many" attached to? Primary key Foreign key Index column Lookup table

Import Import mode is the default and most commonly used storage mode to develop datasets in Power BI. It delivers fast performance thanks to in-memory querying and it also supports specific Power BI service features such as Q&A and Quick Insights.

In which storage mode are tables solely stored in-memory and queries fulfilled by cached data? Import DirectQuery Dual Native

Select the Return type column > Transform > Replace values

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

Datasets, Visualizations, Reports, Dashboards, and Tiles

What are the building blocks of Power BI? Tiles, dashboards, databases, mobile devices Visual Studio, C#, and JSON files Datasets, Visualizations, Reports, Dashboards, and Tiles

Deleting unnecessary rows and columns will reduce the dataset size and it is a good practice to load only necessary data into your data model.

What can be achieved by removing unnecessary rows and columns? It is not necessary to delete unnecessary rows and columns and it is a good practice to keep all metadata intact. Deleting unnecessary rows and columns can damage the structure of the data model. Deleting unnecessary rows and columns will reduce the dataset size and it is a good practice to load only necessary data into your data model.

Adding an index column Index columns contain a list of sequential values that can be used to identify each unique row in a table, which is why they are often used to create unique IDs and form relationships between tables.

What can be useful to create unique IDs and form relationships between tables? Adding a column from examples Grouping data Adding an index column Appending queries

To identify primary keys Use column distribution to identify primary keys (names) within your lookup tables when the number of distinct values is equal to the number of unique values.

What can the column distribution be used for? To identify errors To identify empty values To identify primary keys All of the above

Do some calculations in the original data source Calculations can improve performance by consolidating columns and data types (i.e. strings vs. intgers).

What can you do to improve performance when you are getting data in Power BI? Only pull data into the Power BI service, not Power BI Desktop Use the Select SQL statement in your SQL queries when you are pulling data from a relational database Combine date and time columns into a single column Do some calculations in the original data source

Advanced editor

What functionality lets you see the code that is generated as part of each transformation step? Advanced editor Data profiling Queries pane

The Query Editor writes the corresponding M code for the applied step Data Mashup, or M code, is the formula language that drives Power Query. Whenever you use UI tools to apply a transformation, the corresponding M code is automatically generated.

What happens "under the hood" whenever you apply a transformation in Power Query? The same transformation is applied to the source data The changes are permanent and cannot be modified The Query Editor writes the corresponding DAX code for the applied step The Query Editor writes the corresponding M code for the applied step

The direction that the data flows in a relationship between two tables

What is Cardinality? Cardinality is how long it takes for the data to load Cardinality is the granularity of the data The direction that the data flows in a relationship between two tables Cardinality is a type of visual element

Informed business decisions

What is a key benefit of data analysis? Decisive analytics Informed business decisions Complex reports

To shape & transform data, then load it into Power BI Once you connect to data, the Query Editor lets you shape & transform the data to meet your needs, then load that model into Power BI Desktop

What is the Query Editor used for? As a cloud-based storage option for your organization's data To shape & transform data, then load it into Power BI To create relationships between data and lookup tables To create measures & calculated columns using DAX

Bring data into Power BI Desktop and create a report, share it to the Power BI service, view and interact with reports and dashboards

What is the common flow of activity in Power BI? Bring data into Power BI Desktop and create a report, share it to the Power BI service, view and interact with reports and dashboards Bring data into Power BI mobile, create a report, then share it to Power BI Desktop. Create a report in the Power BI service, share it to Power BI mobile, interact with it in Power BI Desktop. Create a report in Power BI mobile, share it to the Power BI Desktop, view and interact in the Power BI service.

Fact tables store observations or events while dimension tables contain information about specific entities within the data

What is the difference between a fact table and a dimension table? Fact tables store observations or events while dimension tables contain information about specific entities within the data Fact tables contain information about specific entities while dimension tables contain information about observational data Dimension tables tell you about specific roles in Power BI while fact tables tell you information about facts that are associated with those roles in Power BI There is no difference

Primary key Primary keys uniquely identify each row of a table, and match the foreign keys in related data tables.

What is the name of a column or field used to uniquely identify each row of a table? Primary key Foreign key Native key Unique key

All of the above Data profiling tools like column quality, column distribution, and column profile provide a visual way to explore data and get a sense of your dataset composition, while the contextual menus allow you to solve column quality issues.

What is the purpose of data profiling in Power Query? Provide a visual way to explore data Get a sense of your dataset composition To solve column quality issues All of the above

DAX expressions that AVERAGE data will be incorrect

What is the risk of having null values in a numeric column? DAX expressions that MAX data will be incorrect DAX expressions that SUM data will be incorrect DAX expressions that AVERAGE data will be incorrect

Connecting the tables via relationships, based on their common fields

What turns a collection of independent tables into a data model? Connecting the tables via relationships, based on their common fields Connecting to the tables in a single Power BI file Merging the tables into a single "master" table Giving the tables related names

Data type error

What type of import error might leave a column blank? Keep errors Unpivot columns Data type error

Dimension table

What type of table stores details about business entities? Fact table Dimension table Date table Data table

Dataset is too large to be stored in-memory DirectQuery is ideal for large datasets.

When should you use DirectQuery? The source data never changes Company policy states no data source restrictions Dataset is too large to be stored in-memory All of the above

If the file name or location changes Data source = file location

When would you need to access the Data Source Settings? If you need to connect to a new data source If you need to edit and existing query If the file name or location changes All of the above

When you want to overwrite the values of existing columns The Transform tab includes tools to modify existing columns by overwritting its values.

When would you use a tool from the Transform tab over the Add Column tab? When you want to overwrite the values of existing columns When you want to keep the values of existing columns When you want to create new columns Both B & C

Group By Group by allows you to aggregate your data at a different level (like daily data into monthly).

Which Power Query tool can you use to roll-up daily transaction data into monthly transactions? Merging Queries Appending Queries Group By Pivot Columns

Column from examples Columns from examples allow you to add new columns by providing an example value.

Which Power Query tool can you use when you know the outcome of a column you want but don't know which transformation(s) to use? Column from examples Conditional column Custom column Index column

Model The build portion.

Which data analyst task has critical performance impact on reporting and data analysis? Analyze Visualize Model

Column quality Column quality shows the percentage of values within a column that are valid, have errors, or are empty; column profile provides this information in the column statistics as well.

Which data profiling tool can you use to check the number of errors in a column? Column quality Column distribution Column profile Column quality & column profile

Column profile Column profile provides a more holistic view of data within a selected column by providing a sample distribution of the data and column statistics to go along with the data.

Which data profiling tool provides detailed column statistics and value distribution for a selected column? Column quality Column distribution Column profile Column statistics

Column quality, distribution, and profile The VIEW tab includes data profiling tools like column quality, distribution, and profile. Profiling = distribution/profile

Which data profiling tools does Power Query have? Column from examples, custom column, and conditional column Column quality, distribution, and profile Index column and duplicate column Format, extract, and parse

Data analyst

Which data role enables advanced analytics capabilities through reports and visualizations? Data analyst Data scientist Data engineer

Microsoft Dataverse The Dataverse is a cloud-based storage options for your organization's data that you can connect to business applications like Power Apps, Power Automate, and Power Virtual Agents

Which of the following sources lets you connect your data to other business applications? Microsoft Dataverse Microsoft Dataplatform Microsoft Dataflows Microsoft Excel

Merging keeps metrics and dimensions in separate tables Merging data by pulling all the attributes into the fact table itself creates redundant data and utilizes significantly more memory and processing power than creating relationships between multiple small tables.

Which of the following statements is NOT true regarding the use of merged tables instead of data models? Merging creates redundant data Merging uses more memory Merging uses more processing power Merging keeps metrics and dimensions in separate tables

All of the above

Which of the following statements is true regarding filter flow? By default, the filter direction will point from the "one" side of the relationship to the "many" side When you filter a table, the filter context is passed along to all related "downstream" tables. Filters cannot flow "upstream" All of the above

Use a star schema with many-to-many relationships

Which of these is NOT a data model best practice? Use a star schema with many-to-many relationships Contain relationships with one-way filters (vs. bidirectional) Contain tables that each serve a specific purpose, including data (fact) tables and lookup (dim) tables Only include the data you need for analysis (no redundant or unnecessary records or fields)

You should merge tables whenever possible Just because you can merge tables, doesn't mean you should. It's better to keep tables separate and define relationships between them in your data model.

Which of these statements is NOT true about merging queries? Merging queries allows you to join tables based on a common column Merging adds columns to an existing table You should merge tables whenever possible You can merge queries by different join kinds (left outer, inner, etc.)

T-SQL T-SQL or Transact SQL is the query language specific to the Microsoft SQL Server product. It can help perform operations like retrieving the data from a single row, inserting new rows, and retrieving multiple rows. It is a procedural language that is used by the SQL Server.

Which query language do you use to extract data from Microsoft SQL Server? DAX T-SQL MDX

Direct Query With DirectQuery, the data remains in its original source, and any transformations you apply will impact the performance of your report. Import moves it.

Which storage mode leaves the data at the data source? Import Direct Query Dual

Query folding Query Folding is the capability of a Power Query to create a single statement that can transform and execute tasks (such as filtering a thousand rows of data) on the data source side.

Which technology improves performance by generating a single query statement to retrieve and transform source data? Query folding Adding index columns Adding custom columns with complex logic

let & in Opening the advanced editor allows you to see the M code that makes up your query, which consists of two blocks: let (the definition of all variables) & in (the output of your query).

Which two blocks make up the M code that runs your query? do & while if & then for & each let & in

Delete the Remove Duplicates step. Remove Duplicates step is added from FACT internet sales 'CarrierTrackingNumber' column.

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.

Set Data Connectivity mode to DirectQuery. With Power BI Desktop, when you connect to your data source, it's always possible to import a copy of the data into the Power BI Desktop. For some data sources, an alternative approach is available: connect directly to the data source using DirectQuery.

You have an Azure SQL database that contains sales transactions. The database is updated frequently. You need to generate reports from the data to detect fraudulent transactions. The data must be visible within five minutes of an update. How should you configure the data connection? Add a SQL statement. Set Data Connectivity mode to DirectQuery. Set the Command timeout in minutes setting. Set Data Connectivity mode to Import.

Mid Conditional columns allow you to define new fields based on logical rules (IF statements). In this example, the value of 500 is greater that 100, but less than 1000, which is why the output is "Mid".

Based on the conditional column above, what Price Range value would be given to a Unit_Price of 500? Low Mid High Unclassified

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.

From Power BI Desktop, you create a query that imports the following table (first image). You need to configure the table to appear as shown in the following table (second image): 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.

Power Query editor

The primary data preparation tool in Power BI is called what? Report editor Power Query editor Data editor

Group by columns

The process of shaping data by converting your flat data into a table that contains an aggregation value for each unique value in a column is called what? Group by columns Pivot (pivoting a column) Manage aggregations

Fact and dimension tables

The two types of tables in a star schema are what? Active and inactive tables Qualitative and quantitative data tables Fact and dimension tables

One-to-many Single

You are creating a Microsoft Power BI data model that has the tables shown in the following table. The Products table is related to the ProductCategory table through the ProductCategoryID column. You need to ensure that you can analyze sales by product category. How should you configure the relationships from Products to ProductCategory? Cardinality: One-to-many One-to-one Many-to-many Cross-filter direction: Single Both

Total Sales Date Months

You are creating a quick measure as shown in the following exhibit. You need to create a monthly rolling average measure for Sales over time. How should you configure the quick measure calculation? Base value: Month Total Cost Total Order Qty Total Sales Year Date: Date Month Total Sales Year Period: Days Months Quarters Years

In Query Editor, add a calculated column that totals the sales amount column. Modify the query to add a new column that totals the sales. You would not change the data type.

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. Which one is correct? 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.

Transpose Transpose to 'shape'.

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

one-to-many from Customer to Transaction Each customer can have many transactions. For each transaction there is exactly one customer.

You build a report to analyze customer transactions from a database that contains the tables shown in the following table. You import the tables. Which relationship should you use to link the tables? many-to-many between Customer and Transaction one-to-many from Transaction to Customer one-to-many from Customer to Transaction one-to-one between Customer and Transaction

21319 Lasalle Street

You create the following step by using Power Query Editor. Table.ReplaceValue(SalesLT_Address,"1318","1319",Replacer.ReplaceText,{"AddressLine1"}) A row has a value of 21318 Lasalle Street in the AddressLine1 column. What will the value be when the step is applied? 1318 1319 21318 Lasalle Street 21319 Lasalle Street

Create a column by example that starts with 2018-12-31 and set the data type of the new column to Date. To use a built-in-date hierarchy, you need to set the data type of the new column to Date.

You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the date and time each complaint occurred. The data in Logged is in the following format: 2018-12-31 at 08:59. You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy. What should you do? Change the data type of the Logged column to Date. Apply the Parse function from the Date transformations options to the Logged column. Create a column by example that starts with 2018-12-31 and set the data type of the new column to Date. Apply a transform to extract the first 11 characters of the Logged column.

From the Power BI tab in Excel, pin the table.

You have a Microsoft Excel spreadsheet that contains a table named Sales. You need to add the Sales table to a Power BI dashboard as a tile. What do you do? From the Power BI service, import the data from the Excel workbook. From Excel, publish the workbook to the Power BI service. From the Power BI tab in Excel, pin the table. From the Power BI service, upload the Excel workbook.

Get data from a SharePoint Online folder and enter the site URL. Select Combine & Transform, then filter by the folder path to the manufacturing reports library.

You have a Microsoft SharePoint Online site that contain several document libraries. One of the document libraries contains manufacturing reports saved as Microsoft Excel files. All the manufacturing reports have the same data structure. You need to use Power BI Desktop to load only the manufacturing reports to a table for analysis. What should you do? Get data from a SharePoint Online folder, enter the site URL, and then select Combine & Load. Get data from a SharePoint Online list and enter the site URL. Select Combine & Transform, then filter by the folder path to the manufacturing reports library. Get data from a SharePoint Online folder and enter the site URL. Select Combine & Transform, then filter by the folder path to the manufacturing reports library. Get data from a SharePoint Online list, enter the site URL, and then select Combine & Load.

Use Power Query to group the sensor data by hour.

You have a Power BI model that contains sensor data from 500 sensors that return temperature readings each minute. Your reporting requirements contain the need to calculate the average temperature from each sensor at every hour. The reports do NOT show the raw data for each minute. You need to reduce the size of the model to improve performance. What should you do? Create visuals that group the data by hour. Use Power Query to group the sensor data by hour. And a report filter for the Hour column. Remove the rows that contain readings.

Enable Column profiling based on entire dataset. 1500>1000 initial scan. Enable Column profiling. Select Column profile. Select Column profile. "to which you want to target a marketing campaign...." Profile will target with a list of data to select.

You have a prospective customer list that contains 1,500 rows of data. The list contains the following fields: - First name - Last name - Email address - State/Region - Phone number You import the list into Power Query Editor. You need to ensure that the list contains records for each State/Region to which you want to target a marketing campaign. Which two actions should you perform? Open the Advanced Editor. Select Column quality. Enable Column profiling based on entire dataset. Select Column distribution. Select Column profile.

From the Extract menu, click Trim. Whitespace. From the Extract menu, click Clean. Non-printable characters.

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? ErrorCode From the Extract menu, click First Characters. From the Extract menu, click Length. From the Extract menu, click Clean. From the Extract menu, click Trim. SubStatus: From the Extract menu, click First Characters. From the Extract menu, click Length. From the Extract menu, click Clean. From the Extract menu, click Trim.

OData Feed OData is an open protocol (source) that allows the creation and consumption of queryable and interoperable REST APIs in a simple and standard way.

You have a service published to a website. When you connect to the website, you receive the following data. You need to create a query that retrieves the Categories data and the Customers data. Which type of source should you use? JSON Text/CSV OData Feed XML

Select the Zone field. From the Modeling tab, change the Data Category. 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."

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.

Create an active relationship between Sales and Date for the purchase date and an inactive relationship for the ship date. Only one relationship can be active.

You have sales data in a star schema that contains four tables named Sales, Customer, Date, and Product. The Sales table contains purchase and ship dates. Most often, you will use the purchase date to analyze the data, but you will analyze the data by both dates independently and together. You need to design an imported dataset to support the analysis. The solution must minimize the model size and the number of queries against the data source. Which data modeling design should you use? Use the Auto Date/Time functionality in Microsoft Power BI and do NOT import the Date table. Duplicate the Date query in Power Query and use active relationships between both Date tables. On the Date table, use a reference query in Power Query and create active relationships between Sales and both Date tables in the modeling view. Create an active relationship between Sales and Date for the purchase date and an inactive relationship for the ship date.

Create a parameter and update the queries to use the parameter. Use parameters to store connection details such as instance names and database names. This allows you to manage the connections through the Power BI service web portal, or using APIs, at a later stage. Use the icon 'Manager Parameters' and make updates as needed.

You have the following three versions of an Azure SQL database: - Test - Production - Development You have a dataset that uses the development database as a data source. You need to configure the dataset so that you can easily change the data source between the development, test, and production database servers from powerbi.com. Which should you do? Create a JSON file that contains the database server names. Import the JSON file to the dataset. Create a parameter and update the queries to use the parameter. Create a query for each database server and hide the development tables. Set the data source privacy level to Organizational and use the ReplaceValue Power Query M function.

Append Queries Append is used for same structure table, combine vertically.

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

Configure the Data source settings.

You have two Microsoft SQL Server database servers named SQLProd and SQLDev. SQLDev contains the same tables as SQLProd, but only a subset of the data in SQLProd. You create a new Power BI Desktop model that uses 120 tables from SQLDev. You plan to publish the Power BI file to the Power BI service. You need to connect the model to the tables in SQLProd. The solution must minimize administrative effort. What should you do from Query Editor before you publish the model? Create a new connection to SQLProd, and then import the tables from SQLProd. Delete the existing queries, and then add new data sources. Configure the Data source settings. Edit the source of each table query.

One-to-many Single

You have two tables named Customers and Invoice in a Power BI model. The Customers table contains the following fields: - CustomerID - Customer City - Customer State - Customer Name - Customer Address 1 - Customer Address 2 - Customer Postal Code The Invoice table contains the following fields: - Order ID - Invoice ID - Invoice Date - Customer ID - Total Amount - Total Item Count The Customers table is related to the Invoice table through the Customer ID columns. A customer can have many invoices within one month. The Power BI model must provide the following information: - The number of customers invoiced in each state last month - The average invoice amount per customer in each postal code You need to define the relationship from the Customers table to the Invoice table. The solution must optimize query performance. What should you configure? Cardinality: Many-to-many Many-to-one One-to-many One-to-one Cross-filter direction: Both Single

From Power BI Desktop, create a new role that has the following filters. [CountryRegionName] = "United States" [ProductCategory] = "Clothing" "&&" syntax would complicate the calculation.

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"

Data 2 Data 4 XLSX and CSV will work with Power BI service Premium licence.

You plan to create several datasets by using the Power BI service. You have the files configured as shown in the following table. You need to identify which files can be used as datasets. Which two files should you identify? Data 1 Data 2 Data 3 Data 4 Data 5

Imported Imported for common web locations, 'live connection' for SSAS. It is ideal in this situation where there are not complications to the datasource.

You plan to populate a table in a Power BI dataset with data from a Microsoft SharePoint Online list. Which storage mode will be used? DirectQuery live connection Composite Imported

Data Gateway Data Gateways are required for on-premises data sources, like SSAS Tabular live connections.

You plan to publish your SSAS Tabular (live connection) data model to Power BI Service. What must be used in order for this to be possible? Data Gateway Dual Storage Mode Parameters Admin Privileges

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

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. 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.

Location Dimension Dimension for Fact.

You preview a single table into Power Query Editor in Power BI Desktop. The table contains the following columns: LocationName, LocationID, Temperature, and DateTime. You need to convert the table into a properly formed star schema. To which table should you add the LocationName field? Temperature Measurements Fact Date Dimension Location Dimension Location Fact

9 10

You view a query named Transactions as shown in the following exhibit. The query gets CSV files from a folder. There are _ CSV files: 9 10 25 90 1,000 Removing duplicates based on the Date column will reduce the dataset to _ rows: 9 10 25 90 1,000

Unpivot "Pivoting" is a fancy way to describe the process of turning distinct row values into columns ("pivoting") or turning columns into rows ("unpivoting").

You work as an analyst at Cat Slacks and you've just been handed a csv file with yearly sales by department. After connecting to it in Power BI, you notice that each year has its own column. Which Power Query tool can you use to turn the multiple "Year" columns into rows? Pivot Unpivot Transpose Group By

Connect Live "You're creating a Power BI report with data from an Azure Analysis Services Cube." Power BI Live Connection is a kind of connection in Power BI. It only connects to three types of data sources (SQL Server Analysis Services Multi-Dimensional, SQL Server Analysis Services Tabular, Power BI Service Dataset). These three types of data sources are based on SQL Server Analysis Services (SSAS) technology.

You're creating a Power BI report with data from an Azure Analysis Services Cube. When the data refreshes in the cube, you would like to see it immediately in the Power BI report. How should you connect? Connect Live Import Direct Query

Retrieve rows from a dataset Create a dataset Add rows to a dataset The Power BI REST API has the following operations: - Dataset operations: Get and create Datasets. - Table operations: Get Tables and update Table schema. - Row operations: Add Rows and Delete Rows. - Group operations: Get Groups

Your company has several developers who plan to create custom solutions that will interact with the API for the Power BI service. Which three operations can the developers achieve by using the API? Retrieve rows from a dataset Create a dataset Add rows to a dataset Refresh an imported dataset Add a member to a row-level security role