Flag if text cell contains date

[Anonymous]

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

[v-jiascu-msft]

Hi Bjorn,

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

Best Regards,

[Anonymous]

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

[v-jiascu-msft]

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,

[Anonymous]

"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

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

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"

Best Regards,

[v-jiascu-msft]

Hi @Anonymous,

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,

[Anonymous]

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

[Anonymous]

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

What delimiter are you splitting the columns by?

[Greg_Deckler]

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