300-Set-1-1-20

You plan to create the Power BI model shown in the image.

The data has the following refresh requirements:

>Customer must be refreshed daily.

>Date must be refreshed once every three years.

>Sales must be refreshed in near real time.

>SalesAggregate must be refreshed once per week.

You need to select the storage modes for the tables.

The solution must meet the following requirements:

> Minimize the load times of visuals.

>Ensure that the data is loaded to the model based on the refresh requirements.

Which storage mode should you select for each table?

• Customer: DirectQuery

  Date: DirectQuery

  Sales: DirectQuery

  SalesAggregate: DirectQuery

• Customer: Dual

  Date: Import

  Sales: DirectQuery

  SalesAggregate: Dual

• Customer: Import

  Date: Import

  Sales: DirectQuery

  SalesAggregate: Dual

• Customer: DirectQuery

  Date: Import

  Sales: DirectQuery

  SalesAggregate: Import

• Customer: Dual

  Date: Dual

  Sales: DirectQuery

  SalesAggregate: Import

Answer explanation

Box 1: Dual -Customer should use the dual storage mode.Dual: Tables with this setting can act as either cached or not cached, depending on the context of the query that's submitted to the Power BI dataset. In some cases, you fulfill queries from cached data. In other cases, you fulfill queries by executing an on-demand query to the data source.Note: You set the Storage mode property to one of these three values: Import, DirectQuery, and Dual.Box 2: Dual -You can set the dimension tables (Customer, Geography, and Date) to Dual to reduce the number of limited relationships in the dataset, and improve performance.Box 3: DirectQuery -Sales should use the DirectQuery storage mode.DirectQuery: Tables with this setting aren't cached. Queries that you submit to the Power BI datasetג€"for example, DAX queriesג€"and that return data fromDirectQuery tables can be fulfilled only by executing on-demand queries to the data source. Queries that you submit to the data source use the query language for that data source, for example, SQL.Box 4: Import -Import: Imported tables with this setting are cached. Queries submitted to the Power BI dataset that return data from Import tables can be fulfilled only from cached data.

Reference:https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-storage-mode

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-storage-mode

ou have a project management app that is fully hosted in Microsoft Teams. The app was developed by using Microsoft Power Apps.You need to create a Power BI report that connects to the project management app.Which connector should you select?

• Microsoft Teams Personal Analytics

• Dataflows

• Dataverse

• SQL Server database

Answer explanation

Data sources in Power BI Desktop.The Power Platform category provides the following data connections:Power BI datasets -Power BI dataflows -Common Data Service (Legacy)Dataverse -Dataflows -Other data sources include Microsoft Teams Personal Analytics (Beta).

Reference:https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-sources

For the sales department at your company, you publish a Power BI report that imports data from a Microsoft Excel file located in a Microsoft SharePoint folder.The data model contains several measures. You need to create a Power BI report from the existing data. The solution must minimize development effort.Which type of data source should you use?

• Power BI dataset

• a SharePoint folder

• an Excel workbook

• Power BI dataflows

Answer explanation

Connect to a SharePoint folder from Power Query Desktop To connect to a SharePoint folder:1. From Get Data, select SharePoint folder.2. Paste the SharePoint site URL you copied in Determine the site URL to the Site URL text box in the SharePoint folder dialog box. In this example, the site URL is https://contoso.sharepoint.com/marketing/data. If the site URL you enter is invalid, a warning icon. warning icon will appear next to the URL text box.Select OK to continue.3. If this is the first time you've visited this site address, select the appropriate authentication method. Enter your credentials and choose which level to apply these settings to. Then select Connect.4. When you select the SharePoint folder you want to use, the file information about all of the files in that SharePoint folder are displayed. In addition, file information about any files in any subfolders is also displayed.5. Etc.Reference:https://docs.microsoft.com/en-us/power-query/connectors/sharepointfolder

You import two Microsoft Excel tables named Customer and Address into Power Query. Customer contains the following columns: ✑ Customer ID ✑ Customer Name ✑ Phone ✑ Email Address ✑ Address ID Address contains the following columns: ✑ Address ID ✑ Address Line 1 ✑ Address Line 2 ✑ City ✑ State/Region ✑ Country ✑ Postal Code Each Customer ID represents a unique customer in the Customer table. Each Address ID represents a unique address in the Address table. You need to create a query that has one row per customer. Each row must contain City, State/Region, and Country for each customer. What should you do?

• Merge the Customer and Address tables.

• Group the Customer and Address tables by the Address ID column.

• Transpose the Customer and Address tables.

• Append the Customer and Address tables.

Answer explanation

A merge queries operation joins two existing tables together based on matching values from one or multiple columns. You can choose to use different types of joins, depending on the output you want.Reference:https://docs.microsoft.com/en-us/power-query/merge-queries-overview

You have two Azure SQL databases that contain the same tables and columns.For each database, you create a query that retrieves data from a table named Customer.You need to combine the Customer tables into a single table. The solution must minimize the size of the data model and support scheduled refresh in powerbi.com.What should you do? Please see image for exhibit.

• Option to use to combine Customer tables: Append Queries

  Action to perform on original two SQL database queries: Delete the queries

• Option to use to combine Customer tables: Append Queries as New

  Action to perform on original two SQL database queries: Delete the queries

• Option to use to combine Customer tables: Append Queries as New

  Action to perform on original two SQL database queries: Disable loading the query to the data model

• Option to use to combine Customer tables: Merge Queries as New

  Action to perform on original two SQL database queries: Duplicate the queries

• Option to use to combine Customer tables: Merge Queries

  Action to perform on original two SQL database queries: Duplicate the queries

Answer explanation

Box 1: Append Queries as New -When you have additional rows of data that you'd like to add to an existing query, you append the query.There are two append options:* Append queries as new displays the Append dialog box to create a new query by appending multiple tables.* Append queries displays the Append dialog box to add additional tables to the current query.Incorrect: When you have one or more columns that you'd like to add to another query, you merge the queries.Box 2: Disable loading the query to the data modelBy default, all queries from Query Editor will be loaded into the memory of Power BI Model. You can disable the load for some queries, especially queries that used as intermediate transformation to produce the final query for the model.Disabling Load doesn't mean the query won't be refreshed, it only means the query won't be loaded into the memory. When you click on Refresh model in PowerBI, or when a scheduled refresh happens even queries marked as Disable Load will be refreshed, but their data will be used as intermediate source for other queries instead of loading directly into the model. This is a very basic performance tuning tip, but very important when your Power BI model grows bigger and bigger.Reference:https://docs.microsoft.com/en-us/power-query/append-querieshttps://radacad.com/performance-tip-for-power-bi-enable-load-sucks-memory-up

In Power Query Editor, you have three queries named ProductCategory, ProductSubCategory, and Product.Every Product has a ProductSubCategory.Not every ProductsubCategory has a parent ProductCategory.You need to merge the three queries into a single query. The solution must ensure the best performance in Power Query.How should you merge the tables? Each merge type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.NOTE: Each correct selection is worth one point.

• Left Table: Product

  Right Table: ProductSubCategory

  Join Kind: Full outer

  Left Table: ProductSubCategory

  Right Table: ProductCategory

  Join Kind: Right outer

• Left Table: Product

  Right Table: ProductSubCategory

  Join Kind: Inner

  Left Table: ProductSubCategory

  Right Table: ProductCategory

  Join Kind: Right outer

• Left Table: Product

  Right Table: ProductSubCategory

  Join Kind: Inner

  Left Table: ProductSubCategory

  Right Table: ProductCategory

  Join Kind: Left outer

• Left Table: Product

  Right Table: ProductSubCategory

  Join Kind: Inner

  Left Table: ProductSubCategory

  Right Table: ProductCategory

  Join Kind: Right outer

• Left Table: Product

  Right Table: ProductSubCategory

  Join Kind: Full outer

  Left Table: ProductSubCategory

  Right Table: ProductCategory

  Join Kind: Right outer

Answer explanation

Box 1: Inner -Every Product has a ProductSubCategory.A standard join is needed.One of the join kinds available in the Merge dialog box in Power Query is an inner join, which brings in only matching rows from both the left and right tables.Box 2: Left outer -Not every ProductsubCategory has a parent ProductCategory.One of the join kinds available in the Merge dialog box in Power Query is a left outer join, which keeps all the rows from the left table and brings in any matching rows from the right table.Reference:https://docs.microsoft.com/en-us/power-query/merge-queries-inner https://docs.microsoft.com/en-us/power-query/merge-queries-left-outer

You are building a Power BI report that uses data from an Azure SQL database named erp1. You import the following tables (see image). You need to perform the following analyses: ✑ Orders sold over time that include a measure of the total order value Orders by attributes of products sold The solution must minimize update times when interacting with visuals in the report. What should you do first?

• From Power Query, merge the Order Line Items query and the Products query.

• Create a calculated column that adds a list of product categories to the Orders table by using a DAX function.

• From Power Query, merge the Orders query and the Order Line Items query.

• Calculate the count of orders per product by using a DAX function.

Answer explanation

A merge queries operation joins two existing tables together based on matching values from one or multiple columns.Join the Orders and the Order Line Items tables.Reference:https://docs.microsoft.com/en-us/power-query/merge-queries-overview

You have a Microsoft SharePoint Online site that contains 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 folder and enter the site URL Select Transform, then filter by the folder path to the manufacturing reports library.

• Get data from a SharePoint list, enter the site URL, and then select Combine & Load.

• Get data from a SharePoint folder, enter the site URL, and then select Combine & Load.

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

Answer explanation

Get Data from SharePoint folder + select Combine & Load to load the data from all of the files in the SharePoint folder directly into your app.Note: Connect to a SharePoint folder from Power Query DesktopTo connect to a SharePoint folder:1. From Get Data, select SharePoint folder.2. Paste the SharePoint site URL you copied in Determine the site URL to the Site URL text box in the SharePoint folder dialog box. In this example, the site URL is https://contoso.sharepoint.com/marketing/data. If the site URL you enter is invalid, a warning icon. warning icon will appear next to the URL text box.SharePoint folder selection.3. Select OK to continue.4. If this is the first time you've visited this site address, select the appropriate authentication method. Enter your credentials and choose which level to apply these settings to. Then select Connect.5. When you select the SharePoint folder you want to use, the file information about all of the files in that SharePoint folder are displayed. In addition, file information about any files in any subfolders is also displayed.6. Select Combine & Transform Data to combine the data in the files of the selected SharePoint folder and load the data into the Power Query Editor for editing. Or select Combine & Load to load the data from all of the files in the SharePoint folder directly into your app.Reference:https://docs.microsoft.com/en-us/power-query/connectors/sharepointfolder

See the image for the exhibit. Which three actions should you perform in sequence?

• From Power BI Desktop, import the data from Excel, and select Table1 and Table2

  From Power Query Editor, remove errors from the table

  From Power Query Editor, select Table1, and then select Remove Duplicates

• From Power BI Desktop, import the data from Excel, and select Table1 and Table2

  From Power Query Editor, remove errors from the table

  From Power Query Editor, select Table1, and then select Remove Duplicates

• From Power Query Editor, merge Table1 and Table2

  From Power Query Editor, remove errors from the table

  From Power Query Editor, select Table1, and then select Remove Duplicates

• From Power Query Editor, append Table2 to Table1

  From Power Query Editor, remove errors from the table

  From Power Query Editor, select Table1, and then select Remove Duplicates

Answer explanation

Reference:https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data

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?

• Apply a transformation to extract the last 11 characters of the Logged column and set the data type of the new column to Date.

• Apply a transformation to extract the first 11 characters of the Logged column.

• Split the Logged column by using at as the delimiter.

• Change the data type of the Logged column to Date.

Answer explanation

Extract the date, which is the first 11 characters.CSV files have no data types.Note: A CSV is a comma-separated values file, which allows data to be saved in a tabular format. CSVs look like a garden-variety spreadsheet but with a . csv extension. CSV files can be used with most any spreadsheet program, such as Microsoft Excel or Google Spreadsheets.Reference:https://www.bigcommerce.com/ecommerce-answers/what-csv-file-and-what-does-it-mean-my-ecommerce-business/