Filter Column Headers Conditionally & Dynamically

Also, feel free to drop a comment or any power query questions.The previous post explained why M code? I will explain how we can apply various functions to help us achieve dynamic and future-proof queries in this and future posts with simple examples.So lets us begin our journey!You can click the link below to get the sample data file for this post.Also, please watch until the end, as there is a bonus example.In Power Query, We can easily apply a conditional filter for rows by clicking on the drop-down next to the header, selecting text filter then selecting contains or any other on the list.Have you thought of how to filter the column headers conditionally?The key is "Remove Column". Select a random column and remove it.The highlighted is column name that deleted. It is within a {}, which means it is a list. Let's delete the step, then remove other columns this time.Look at the highlighted code. Again, the column name is in a list form.When we remove columns or remove other columns, the formulas are in the same form.The only difference in the formula is the syntax in the orange colour.Let me show you how to write a conditional column filtering code with only a minor change to the syntax. Add a custom step, highlight the default code, and then type harsh. The IntelliSense will pop up. We want to refer to the third step, so we select promoted headers. We need the column name in a list to make the selection. So, we need this function to get the column names! So, let's add it to the formula bar.Now you get the column names returned as a list. Now we add "List.Select" function to select items from the list, and we apply "Text.Contains" to help us search for keywords from the list.The underscore in the "Text.Contains" formula means each item in the list.Oops! What's wrong?! Look at the keyword, date. It is case-sensitive!No biggy!! Add "Comparer.OrdinalIgnoreCase" as highlighted and problem solved!Those function combinations allow you to get a list of column names on a conditional basis.We apply Table.ColumnNames to get a list of column names, then use the combination of List.Select and Text.Contains to select from the list containing the keywords.Now, let's copy the formula, excluding the equal sign.Then go to the previous step, and paste the copied formula to the highlighted code.Ta-dah!! It has now become a conditional and dynamical code to select columns.Let us tweak the code a bit. Change the code "Select" to "Remove".It has now excluded any columns with names containing "date".We no longer need to column names step. Let's delete it.This time, let's change the keyword "date" to "ID".Again, column names containing "id" is removed. If you add a logical operator, "not", right between each and the "Text.Contains" formula. Column names that do not contain "ID" will be removed. Apart from Text.Contains, we can also apply those two formulas. Let's replace the "Text.Contains" formula with "Text.EndsWith". Then, you get the same result as those column names end with "ID".Now change it to "Text.StartsWith". No column names start with "ID".Let me show you a bonus example, but let us set up the code for the bonus example.Let's do a minor update by changing the highlighted code to "Table.Unpivot"and defining the unpivot column names, "ID Type", and "ID hash".In short, we can apply the below combination functions, replacing the static list code within the generated syntax, and then we can easily create a dynamic syntax.Thank you for watching, and I hope you find this post helpful.Please don't forget to click like if you like the post.