Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
UsePowerBI
Post Prodigy
Post Prodigy

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!

1 ACCEPTED SOLUTION
Vera_33
Resident Rockstar
Resident Rockstar

Hi @UsePowerBI 

 

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]))

 

View solution in original post

1 REPLY 1
Vera_33
Resident Rockstar
Resident Rockstar

Hi @UsePowerBI 

 

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]))

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!