Forum Discussion
Delete multiple columns with condition - Power Query
Dear Community !
My data has columns names as the first day of each month (ex: 1/1/2019, 2/1/2019, 3/1/2019 etc) - 12 Columns for an year.
Need your help to figure out how to remove columns whose names DO NOT CONTAIN current year (2023 in this case), however, I don't want to hardcode the year. Currently I am using below, which solves the purpose, by using multiple lines for each year, however, I was looking for one PQ function.
Any help is welcome ! Thanks in advance !
= Table.RemoveColumns(#"Changed Type1",List.FindText(Table.ColumnNames(#"Changed Type1"),"2019"))
- To remove the columns that do not contain the current year dynamically, you can use the following Power Query function: = Table.SelectColumns(#"Changed Type1", List.Select(Table.ColumnNames(#"Changed Type1"), each Text.Contains(_, Text.From(Date.Year(DateTime.LocalNow())), Comparer.OrdinalIgnoreCase))) This formula uses the Text.Contains function to check whether the column name contains the current year dynamically by using the Date.Year and DateTime.LocalNow functions. It then selects only the columns whose names contain the current year using the List.Select function, and returns the filtered table using the Table.SelectColumns function. This should help you achieve your goal of removing columns dynamically without hardcoding the year. Let me know if you have any further questions.
2 Replies
- To remove the columns that do not contain the current year dynamically, you can use the following Power Query function: = Table.SelectColumns(#"Changed Type1", List.Select(Table.ColumnNames(#"Changed Type1"), each Text.Contains(_, Text.From(Date.Year(DateTime.LocalNow())), Comparer.OrdinalIgnoreCase))) This formula uses the Text.Contains function to check whether the column name contains the current year dynamically by using the Date.Year and DateTime.LocalNow functions. It then selects only the columns whose names contain the current year using the List.Select function, and returns the filtered table using the Table.SelectColumns function. This should help you achieve your goal of removing columns dynamically without hardcoding the year. Let me know if you have any further questions.
THANK YOU Adamboer ! Did the job precisely 🙂