Reply
Highlighted
Frequent Visitor
Posts: 7
Registered: ‎12-07-2018
Accepted Solution

Flag if text cell contains date

I am currently working on reporting some data that is extracted from AX.

 

Due to lack-of-proper AX configuration, the data is suffering from poor quality of entered data, missing or wrong values.

 

I have a text column (usually 100-200 characters) that contains description of a specific sales order. The cells sometimes contains dates of sales that were supposed to be input into another column.

 

I would like to make a conditional column in power query, with an IF statement that flags if the Description columns contains date ranges in the following ways: DD-MM-YYYY or DD.MM.YYYY. Is this possible? Or is there another viable method?

 

Kind regards

Bjorn


Accepted Solutions
Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

Re: Flag if text cell contains date

Hi @bjornsivertsen,

 

Please try the formula below and download the demo from the attachment. Your actual data could be more complicated. The basic idea of this solution is splitting the text and checking if the string can be converted to date. Anyway, you can try it out. The potential issue could be the performance. 

Would you like a solution based on Python or R? If you can run them, they could be better.

 

if [Column1.2] = null then "Ignore" else let currentColumn = [Column1.2] in 
if List.Accumulate(Text.Split(currentColumn, " - "), 0,
(sign, value) => if (try Date.FromText(value, "en-GB") otherwise #date(9999, 12,31)) <> #date(9999, 12, 31) then sign + 1 else sign) > 0 then "Yes"
else if List.Accumulate(Text.Split(currentColumn, "–"), 0,
(sign, value) => if (try Date.FromText(value, "en-GB") otherwise #date(9999, 12,31)) <> #date(9999, 12, 31) then sign + 1 else sign) > 0 then "Yes"
else if List.Accumulate(Text.Split(currentColumn, "-"), 0, (sign, value) => if (try Date.FromText(value, "en-GB") otherwise #date(9999, 12,31)) <> #date(9999, 12, 31) then sign + 1 else sign) > 0
then "Yes" else "unknown"

 

Flag-if-text-cell-contains-date

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Attachment

All Replies
Super User
Posts: 10,462
Registered: ‎07-11-2015

Re: Flag if text cell contains date

You should be able to do this by testing the left 2 digits to see if they are numeric for example. I think that @ImkeF could probably provide specifics.

 

You could use Text.Middle: https://docs.microsoft.com/en-us/powerquery-m/text-middle

 

Then use Number.From and test for error condition:

 

https://docs.microsoft.com/en-us/powerquery-m/number-from

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

Re: Flag if text cell contains date

Hi Bjorn,

 

Please also share some sample data which can show their specificities.

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 7
Registered: ‎12-07-2018

Re: Flag if text cell contains date

Hi @v-jiascu-msft

 

Description

Xink Cloud Service and support 1 year - Campaign/eMailSignature 'classic' (500)Periode: 01.10.18-30.09.19
Basic Supp./Subsc. VMware vSphere 6 w/Operations Management Enterprise Plus 1 YearPerioden:17-10-2018 - 16-10-2019
VisualSVN Server Enterprise Edition 1 Year maintenance renewalperiod:April 27, 2018 - April 27, 2019

 

I've inputted some examples above, as you can see the comment usually ends with the date, but these come in in various forms. And I would like to catch all of them.

 

And while I think automatically inputting the right formatted value into a standardised date format, I would just like Power BI to create a column flagging that dates are present, so my Operations colleague can go and correct the wrongly typed info in Ax.

 

Kind regards

Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

Re: Flag if text cell contains date

Hi Bjorn,

 

One quick question: would you just like a flag that shows there are dates in the comments? For example, "Yes". 

Should Power BI extract them? 

BTW, what should we do with the third format? "April 27, 2018".

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 7
Registered: ‎12-07-2018

Re: Flag if text cell contains date

"One quick question: would you just like a flag that shows there are dates in the comments? For example, "Yes". "

 

Yes, that would be perfect. It will be used to push back to the operations team to fix the source AX data, so dates are placed in date fields, so reporting becomes more meaningful.

 

"BTW, what should we do with the third format? "April 27, 2018"."

 

Preferably the same thing as with the two other formats.

 

If it is possible, I also got rows that has cell contents similarly to the below, where the date field info is in the middle of the text.

 

"Think Cell Chart - Annual lic.Period:2018-09-16 – 2019-03-15new total 20"

 

If it's possible for the solution to catch these as well, it would be perfect.

 

Thank you in advance @v-jiascu-msft

Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

Re: Flag if text cell contains date

Hi @bjornsivertsen,

 

Please try the formula below and download the demo from the attachment. Your actual data could be more complicated. The basic idea of this solution is splitting the text and checking if the string can be converted to date. Anyway, you can try it out. The potential issue could be the performance. 

Would you like a solution based on Python or R? If you can run them, they could be better.

 

if [Column1.2] = null then "Ignore" else let currentColumn = [Column1.2] in 
if List.Accumulate(Text.Split(currentColumn, " - "), 0,
(sign, value) => if (try Date.FromText(value, "en-GB") otherwise #date(9999, 12,31)) <> #date(9999, 12, 31) then sign + 1 else sign) > 0 then "Yes"
else if List.Accumulate(Text.Split(currentColumn, "–"), 0,
(sign, value) => if (try Date.FromText(value, "en-GB") otherwise #date(9999, 12,31)) <> #date(9999, 12, 31) then sign + 1 else sign) > 0 then "Yes"
else if List.Accumulate(Text.Split(currentColumn, "-"), 0, (sign, value) => if (try Date.FromText(value, "en-GB") otherwise #date(9999, 12,31)) <> #date(9999, 12, 31) then sign + 1 else sign) > 0
then "Yes" else "unknown"

 

Flag-if-text-cell-contains-date

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
Frequent Visitor
Posts: 7
Registered: ‎12-07-2018

Re: Flag if text cell contains date

Thank you @v-jiascu-msft, it will definitely work for now!

 

What delimiter are you splitting the columns by?

Community Support Team
Posts: 7,619
Registered: ‎05-02-2017

Re: Flag if text cell contains date

Hi @bjornsivertsen,

 

The delimiters depend on your text. There are four I used. They are the second parameter of Text.Split. The list of them is ":", " - ", "–", "-". 

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 7
Registered: ‎12-07-2018

Re: Flag if text cell contains date

Thank you @v-jiascu-msft, I must have been blind from the lack of coffee, as it's right there!