How should you complete the Power Query M code?
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.
Answer:
Explanation:
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
Latest DA-100 Dumps Valid Version with 114 Q&As
Latest And Valid Q&A | Instant Download | Once Fail, Full Refund