M code - list select date or text length = 9

Power Query
1

Dear DNA Team,

I need a small help with extracting the date from the list.

image

I am able to extract the date position from the list select if the date is in the third row but sometimes it could be changed:

Therefore I wrote the below mcode hoping it would work:

List.Select([Custom.2], each _ is date )

but the result that I get is blank:

image

I thought maybe I could use text.lenth = 9 formula inside of list.select but again this didn’t work.

Could you please advise?

Thank you,

Matt

2

Hi @Matty,

For the date text.length should be 8 not 9 :wink:
Give this a go to eliminate text strings with the same length.

List.Select( [Custom.2],  
    each (Text.Length(_) = 8 
    and ( Value.FromText(_) is number or Value.FromText(_) is date ))
)

I hope this is helpful

2 Likes
3

Thank you very much @Melissa,

I was not able to reference the column in the formula.

I understand your code and I will for sure use it for future reference.

“_” - relates to each separate row field in the list?

Thank you!

Matty

4

Hi @Matty,

[Custom.2] should be replaced by your list reference (I assumed this was the name based on your snip above). And yes, with the underscore you’re accessing each element in the list.

For me Value.FromText(“30.06.21”) is evaluated to a number, whereas Value.FromText(“30/06/21”) or Value.FromText(“30-06-21”) are evaluated to dates

Glad to hear you’ve found it helpful

1 Like
5

@Melissa

Thank you for an explanation :slight_smile: very helpful for future reference!

Thank you,

Matty