Microsoft DA-100 Exam Practice Questions (P. 2)
- Full Access (94 questions)
- Six months of Premium Access
- Access to one million comments
- Seamless ChatGPT Integration
- Ability to download PDF files
- Anki Flashcard files for revision
- No Captcha & No AdSense
- Advanced Exam Configuration
Question #6
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:
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
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
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:
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
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
All Pages