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.

View Answer

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