We cannot apply field access to the type Text

[Dan Bliss]

The following gets the error - "We cannot apply field access to the type Text"


Suppose a column, [filename], text field with values that include a date, such as:

2016-02-09-statements-8345.xlsx
Chase200902Feb09.xlsx

Suppose that the "format" of the embedded date changes and there are many different date formats over all rows, or the data is unknown and thus the date format is unknown.

Programming in Power Query is simple if we know all file names begin with "Chase".
If there are 2 date fomats, as shown here, then I would use a filter, separating those starting with "Chase" from those names that contain "statements".
Is there a way to program in PQ if we only know the file name contains a date, but we don't know what the date delimiters are - such as "statements" or "Chase", or even "-"?


One idea I had was to load [filenames] to a table in Excel, then have User add Start and Length values for each row. Start & Len values then be used in function Text.Middle() to extract the date. My thinking is it would be easier for a human to fill in a table of Left1Start & Left1Len values then try to build M steps that handle a universe of possible date formats. (But maybe there's a simpler way?)


Anyway, so here's what my table looks like for these 2 filenames:

FullFileName	Left1Start	Left1Len	Left2Start	Left2Len
{type text}	{type Int64}	{type Int64}	{type Int64}	{type Int64}
2016-02-09-statements-8345.xlsx	0	10	0	0
Chase200902Feb09.xlsx	5	6	14	2

In an initial step to transform the FullFileName, the following produces the error described:
Table.TransformColumns(#"Changed Type", {{"FullFileName", each Text.Middle(_, [Left1Start], [Left1Len])}})

Any thoughts as to why?​

 

[rebekah]

This thread is closed per user request. User contacted Excelguru directly and asked that this thread be closed as he found it was already answered.