Question 6 of 94

HOTSPOT -

You have an API that returns more than 100 columns. The following is a sample of column names.

✑ client_notified_timestamp

✑ client_notified_source

✑ client_notified_sourceid

✑ client_notified_value

✑ client_responded_timestamp

✑ client_responded_source

✑ client_responded_sourceid

✑ client_responded_value

You plan to include only a subset of the returned columns.

You need to remove any columns that have a suffix of sourceid.

How should you complete the Power Query M code? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Hot Area:

    Correct Answer:

    Box 1: Table.RemoveColumns -

    When you do ג€Remove Columnsג€ Power Query uses the Table.RemoveColumns function

    Box 2: List.Select -

    Get a list of columns.

    Box 3: Text.Contains -

    Example code to remove columns with a slash (/):

    let

    Source = Excel.Workbook(File.Contents("C: Source"), null, true),

    #"1_Sheet" = Source{[Item="1",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(#"1_Sheet", [PromoteAllScalars=true]),

    // get columns which contains any slash among values

    ColumnsToRemove =

    List.Select(

    // get a list of all columns

    Table.ColumnNames(#"Promoted Headers"),

    (columnName) =>

    let

    // get all values of a columns

    ColumnValues = Table.Column(#"Promoted Headers", columnName),

    // go through values and stop when you find the first occurence of a text containing a slash

    // if there is a value with a slash, return true else false

    ContainsSlash = List.AnyTrue(List.Transform(ColumnValues, each Text.Contains(_, "/"))) in

    ContainsSlash -

    ),

    // remove columns

    Result = Table.RemoveColumns(#"Promoted Headers", ColumnsToRemove) in

    Result -

    Reference:

    https://community.powerbi.com/t5/Power-Query/Remove-columns-containing-a-certain-value/td-p/759657

Question 7 of 94

DRAG DROP -

You are building a dataset from a JSON file that contains an array of documents.

You need to import attributes as columns from all the documents in the JSON file. The solution must ensure that date attributes can be used as date hierarchies in

Microsoft Power BI reports.

Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Select and Place:

    Correct Answer:

    Step 1: Expand the records.

    First Open Power BI desktop and navigate to Power Query, import the JSON file, then load the data, click on the record to expand it and to see the record and list.

    Step 2: Add columns that use data type conversions.

    Step 3: Convert the list to a table

Question 8 of 94

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

The Customer ID and Address ID columns represent unique rows.

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?

    Correct Answer: A

    To create a query that includes the City, State/Region, and Country for each customer, you need to combine the Customer and Address tables based on the Address ID column. Merging the tables on the Address ID allows you to add the relevant address information (City, State/Region, and Country) to each customer's row, resulting in a single row per customer that contains all necessary details.

Question 9 of 94

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?

    Correct Answer: B

    The correct approach to easily switch between different database servers in Power BI is to create a parameter and update the queries to use the parameter. This method allows you to define the database server names as parameters and then modify the parameter value to switch between development, test, and production environments. This is an efficient way to dynamically manage data sources without modifying the underlying queries directly each time you need to switch environments.

Question 10 of 94

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?

    Correct Answer: A

    To analyze the complaints by the logged date and use a built-in date hierarchy, you need to ensure that the column containing the date information is recognized as a date data type. Given that the Logged column contains date and time information in the format '2018-12-31 at 08:59', you would need to convert this column directly to the Date data type. Selecting the data type as Date allows the built-in date hierarchy to function correctly without the need for additional transformations.