Home » Microsoft » DA-100 v.2 » 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.
Hot Area:
Correct Answer:
Explanation/Reference:
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