Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filtering columns in M

 Hello

 

I have some column headers which are text and some which are dates. I want to only keep all the non-dates and the dates that are earlier than a specific date.

 

So I tried:

#"Filter" = Table.SelectColumns(#"PromotedHeaders",List.Select(Table.ColumnNames(#"PromotedHeaders"), each (Date.FromText(_)=null or Date.FromText(_)<=Date.AddDays(DateTime.Date(DateTime.LocalNow()),-2)))

 

But I get a 'cannor parse as date error'. How do I keep these columns that cannot be parsed as dates?

 

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

     

    When you try Date.FromText on those columns with non-dates, it throws error, you can wrap try..otherwise, so slightly modify yours, call it dateFromColumn to avoid more typing

    #"Filter" = 
Table.SelectColumns(#"PromotedHeaders",List.Select(Table.ColumnNames(#"PromotedHeaders"), each 
[dateFromColumn = try Date.FromText(_) otherwise null, 
res =dateFromColumn = null or dateFromColumn<=Date.AddDays(DateTime.Date(DateTime.LocalNow()),-2)][res]))

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous 

     

    When you try Date.FromText on those columns with non-dates, it throws error, you can wrap try..otherwise, so slightly modify yours, call it dateFromColumn to avoid more typing

    #"Filter" = 
Table.SelectColumns(#"PromotedHeaders",List.Select(Table.ColumnNames(#"PromotedHeaders"), each 
[dateFromColumn = try Date.FromText(_) otherwise null, 
res =dateFromColumn = null or dateFromColumn<=Date.AddDays(DateTime.Date(DateTime.LocalNow()),-2)][res]))