Question 39

Explanation

After reading data from the source, the first step is to list all the columns in the table, so we can remove columns that match the given pattern.

Table.Column returns a specific column of data as a list.

We need a list of column names, not any columns’ data. Table.ColumnNames returns the column names of the table as a list.

Reference Link: https://docs.microsoft.com/en-us/powerquery-m/table-column

https://docs.microsoft.com/en-us/powerquery-m/table-columnnames

So, Box 1 -> Table.ColumnNames

Once we have a list of column names, we have to search the list for a specific pattern: columns that have the prefix Test_. List.FindText returns a list of values from the list that contains the required text.

Whereas, List.First returns the first item in the list.

Reference Link: https://docs.microsoft.com/en-us/powerquery-m/list-findtext

https://docs.microsoft.com/en-us/powerquery-m/list-first

Although List.FindText searches for the pattern in the entire text (not just at the beginning), it is a more correct choice than List.First.

So, Box 2 -> List.FindText.

Finally, we need to remove these extracted columns that match the pattern from the source table. Table.RemoveColumns removes the list of columns (in SearchCol) from the source.

List.RemoveItems is not suitable because it works on a list, not a table. We need to remove columns from the table, not items from a list.

Reference Link: https://docs.microsoft.com/en-us/powerquery-m/table-removecolumns

https://docs.microsoft.com/en-us/powerquery-m/list-removeitems

So, Box 3 -> Table.RemoveColumns.

Option B is the correct choice.

You can view the entire M code in the Advanced editor.

PBIX File Link: Remove columns that match a pattern