Differentiating dates in Power Query

cr731

Well-known Member
Joined
Sep 17, 2010
Messages
611
I have a column of values such as

MyColumn
TextValue1
TextValue2
TextValue3
Jan 2016
Feb 2016
2020
2021


... I need to keep only those values that are formatted like mmm yyyy, so from the above, would keep

Jan 2016
Feb 2016

At first, I tried adding a column in Power Query,

=Date.FromText([MyColumn])

And then removing errors.

However, this keeps the 2020 and 2021 values, as PQ still sees those as dates.

I don't want to use the length as a test (such as testing that the length is 8 for mmm yyyy format), because it is possible that there could be leading/trailing spaces, like

" Jan 2015"
"Mar 2014 "

And I would need to keep those.

How else can I keep dates that are mmm yyyy but exclude yyyy entries?

Thanks
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I still think you could use length... like... an IF statement to make sure it is 7+ characters long?
 
Upvote 0
I would basically do the same:
n. Menu: Icon/Function
1. Transform: Format/Trim ==> " Jan 2015" --> "Jan 2015"
2. Transform: Date/Parsed date
3. Filter for errors
==> only mmm yyyy remained (in your example, in PowerBI)
//Ola
 
Upvote 0
Your request to keep the dates with leading spaces sounds a bit scary ... - but can be achieved by simply adding a filter to what you've done already:

So first duplicate your column and convert to date and second filter only those whose value in the original column is longer than 7 characters.
 
Upvote 0
Just read...a solution for removing blanks - if needed.
//Ola


Problem:
101015_1928_powerqueryt15.png


Solution:
Post: https://bondarenkoivan.wordpress.co...n-using-own-function-in-power-query/#more-268
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top