1

What is Inner Join?

This is the merging join type that returns only the rows existing in both the left and right table.

Explain the Text.Replace M function

it returns the result of replacing all occurrences of an existing text value into a new text value

You have a report named Sales in an app workspace named Sales_BI. User_01 is a member of the Sales_BI.You have enabled the option "Allow users to build new content using the underlying dataset" from the report settings.What will happen to the underlying dataset? Select the true statements.

-Row-level security restrictions on the dataset are in effect -You haven't made a copy of the dataset. The dataset still resides in its original location

What is a conditional column?

A conditional column is a column based on a condition imposed on an existing column.

You have a report named Sales_01. You plan to publish Sales_01 to a Blog, but you need to be sure that the report will preserve its size as a set on the report page.Which View Mode should you set for the Sales_01 report?

Actual Size.

You have created a query to append the rows of three different tables that contains product data into one table named Products.Now you need to set-up a new ID Column to be sure that every row in the Products table contains a unique value.How can you meet the goal?

Add an Index Column to Products Table Select a Product Table Column and click Remove Duplicates.

Your Organization plan to create a Power BI App which would include Power BI dashboards and other custom content.The App will be available to all Organization users. You need to be sure that all the PowerBI users will have access to the App.

Ask the PowerBI admin for enabling permission to install the app automatically to all users.

After you have created a stacked bar chart to display CourseName by StartingDate, you notice that the axis shows all the individual dates. You want to set the drill down in the chart to display CourseName by year, by week, and day.What is the first task to do?

Create a new table that has columns for the date, year, week, and day. When a column is formatted as a date, Power BI will display a drillable hierarchy of year, quarter, month and days, but not of week. You have to create a column that includes it.

You have a Microsoft Excel file named Enrollments.csv that contains data about Student Enrollments by Region.After you have imported the Excel file to Power BI Desktop, you create a bar chart showing the Enrollments amount by region.Unfortunately, the Enrollments amount value appears as the count of Enrollments amount instead of the sum of Enrollments amount for each region.You need to modify the query to ensure that the data appears correctly.How can you fix the issue?

Change the Data Type of Enrollments amount column to Whole Number.

Which data profiling feature shows you the percentage of valid, errors or empty values in a column?

Column Quality

You plan to create a Corporate Power BI Desktop Project that will be shared with the entire organization.You need to customize the report with both the corporate colors of your organization and a set of custom icons.How can you meet the goal?

Configure a JSON file and then import it in Power BI Desktop as Custom Theme

Your principal asks you to print some Power BI reports in order to physically share them with the other colleagues during the next meeting. When you start to print the reports, you notice that some tables show less rows than they really have. How can you solve this issue?

Create a Paginated Report.

You manage a list of reports for the sales team of your company. The company operates across different regional markets and every region has its sales manager.You need that the sales managers can interact with the data from their region. In addition, you want that sales managers can't modify the report's layout.Note that the datasets use row-level security (RLS).How can you meet the goal?

Create a Power BI app that contains all the reports and give access permissions to the sales managers.

You have imported a data source in Power BI Desktop, now you plan to use Power Query to add some data transformations. You notice that the data model presents a huge list of queries that make it difficult to organize the transformation steps. Which best practice can you adopt in Power Query to solve this issue?

Create a group of queries to classify the data-transformations.

You have a table named Enrollments with the following columns:- StudentID;- CourseID;- Date;- CourseName;- FeePaid.You create a report that contains a Stacked Column Chart that shows the count of Students by CourseName, then you want to customize the information placed in the visualization tooltip for the chart.What should you do first?

Create a new report page and set it as Tooltip.

Your Power BI model contains two tables named StoreVisits and Date. After you have created a measure to calculate the number of Store Visits, you use it in a report visualization as shown in the picture. You found that the total number of customer visits was 60,000 and that there were only 5,000 customer visits in August. You check that the total number of visits was 90,000 and that there were only 10,000 visits in October.How can you fix the report visualization?

Create a relationship between the StoreVisits and the Date table.

You plan to use R Scripting in Power BI Desktop to create several visualizations.Which actions should you perform to use R scripting in Power BI Desktop?

Download and install Microsoft R on the computer that has Power BI Desktop installed.

You have created a Power BI dashboard that contains visualizations of sales data.You want to enable sample questions that users can ask when using the Q&A option.What do you need to modify in the Power BI Settings?

Datasets

You are analyzing the Customer Sales monthly report and you notice that the Churn Rate metric is strangely increased.You want to understand which variables may affect this result.Which solutions can help you to explore the possible causes in Power BI Desktop?

Decomposition tree visual & Use the Key Influencer visual

You have a dashboard named Sales_01. Q&A is enabled on the dashboard. When users try to get the count of clients, the query fails. Instead, when users ask for the count of subscribers, they receive the expected results. You want to ensure that both queries will return the same results.Which action should you perform?

Edit Synonyms from Power BI Desktop.A good practice to improve the Q&A experience is adding synonyms from the Power BI Desktop Model view.

Consider a Power BI report that contains a Pie chart and a Line chart on the same page. The Line chart displays the total Enrollments by Year, while the Pie chart displays the total Enrollments by course.You want to ensure that when you select a year on the Line chart, the Pie chart remains unchanged.How can you perform this task?

Edit the interactions from the Format menu.

Consider a report in Power BI Desktop that contains several donut charts and a date slicer.You plan to create a slide show that displays the donut charts filtered by different years. You want that the slide show can be used from Power BI Service.What should you do before publishing the report?

Filter the donut charts by using the slicer, then create bookmarks.

You are configuring a Gauge Chart. Where do you set the Goal?

Format settings.

You have an app workspace. You collaborate in the app workspace with a user named User_01. User_01 is a member of the workspace. You plan to create a report that retrieves data from a Microsoft SQL Server database. You want that User_01 can edit the report both from Power BI Desktop and from powerbi.com.Select the right order of the actions to achieve the goal.

From Power BI Desktop, add a data source - From Power BI Desktop, create a report - From Power BI Desktop, publish the report to the Power BI Service.

The tables come from a Microsoft SQL Server database.The Enrollments table contains more than two million rows. You plan to import a sample of data from the Enrollments table in Power BI Desktop.There are two ways to achieve the goal from the following options, select them.

From Query Editor, filter the table by Enrollment_date & From Query Editor, add a SELECT statement that uses a WHERE clause.

Consider a table named Enrollments that contains a column named StudentID. The Data Type of StudentID is "whole number". After you have imported tables in Power BI Desktop, you discover that the StudentID column contains several non-numeric values. You want the non-numeric values in the StudentID column set to 0.How can you achieve the goal?

From Query Editor, select the StudentID column. Click Replace Errors and set a value of 0.

A Power BI user in your organization has published a report to the Web. The report contained sensitive data.What can you do to prevent the reports from being published on the Web?

From the Power BI Admin portal, disable the Publish to web setting.

You have a table named 'Enrollments' stored on a Microsoft Excel spreadsheet.You want to add the Enrollments table to a Power BI dashboard as a tile.What is the tile configuration?

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

Your workspace contains 10 dashboards. You want to allow users to find data by using natural language queries.What should you do to meet the goal?

From the properties of the datasets, modify the Q&A settings.

Your organization plan to use the Power BI Service to get several visualizations from data stored in a custom application.The developers ask for pushing data into the Power BI Service from the custom app.How can you ensure this task?

Go to dev.powerbi.com/apps and register an application.

Explain the Table.Trasform Column types function

In order to realize the relationship, the two columns must be set with the same data type: the Table.TrasformColumnTypes serves this purpose.

Your Power BI report contains a clustered bar chart that has Sales[ReturnAmount] as the value and Product[ProductKey] as the axis.You want to improve the visual by adding a horizontal line that shows which products have a return amount above the median amount.How could you reach the goal?

In the Analitycs pane for the clustered bar chart, add a Percentile line set to 50%.

Consider a table named Students with the following columns:- StudentID;- StudentName;- City;- ProvState;- Country.You are creating a Power BI report that includes a bar chart to display the number of students by location. You want that the report will show the number of students by Country, and drill down to ProvState, and in the end to City.Select the right configuration of the drill down in the bar chart.

In the Axis field, add all the columns in the following order: Country at the top, followed by ProvState and City.

You plan to publish a report from Power BI Desktop to Power BI Service.The report retrieves data from an Azure SQL Database. How can you ensure that users of your organization will see the current data when they access the report on the Power BI Service?

In the Power BI service's Settings page, select the Datasets tab, choose the dataset that uses DirectQuery, and select Edit credentials.

Your organization owns a consistent number of dashboards on Power BI. Your goal is to ensure that when users browse the list of the available dashboard, they can see the ones that contain Personal Identifiable Information.Which function best fit your needs?

Microsoft Information Protection sensitivity labels.

Is the Drill Down feature currently available for dashboards experience?

No

You plan to create a custom visualization for Power BI. What do you install first?

Node.js

You have a Power BI app workspace that contains 30 reports shared with 55 users of your organization.You plan to start a reports maintenance program. You need to know which reports present a critical usage.How can you achieve this task?

Request to the Power BI admin enabling usage metrics for content creators.

Your company stores sales data on a Microsoft SQL Server. You are responsible for creating a Power BI report, ensuring the quality and completeness of the sales data. In particular, you must be sure that the report doesn't show negative amounts for sales data.How can you review data in Power Query before creating the data model?

Select Column profile, and then select the total_sales column.

You want to replace the "-" value in the DISCOUNT_CODE column with the text "NO_DISCOUNT".Which M function completes the following formula?

Table.ReplaceValue

Explain the Table.ReplaceValue function

Table.ReplaceValue replaces an existing value with a new value in the specified columns of a table.

The Sales table contains one million records per month. You want to minimize the data model size. Plus, you want to count sales by Date and Store. How can you meet the two goals?

Use the Group by function in Power Query, aggregating sales by DateKey and StoreKey using Count Rows as operation.

Explain the Text.Start M function

necessary to give to the Text.Replace function the position of the replaced new values

Explain the M function Table.Distinct

thanks to the table function Table.Distinct, you can remove duplicated rows from the selected table.

What does Text.AfterDelimiter return?

the portion of a text after the specified delimiter: in this case, our delimiter is "x".

What does RelativePosition.FromEnd return?

working as an index, to specify which occurrence of the delimiter should be considered and that the indexing needs to be done from the end of the input.

What is the Table.Distinct function?

you can remove duplicated rows from the selected table.