Expression.Error: We cannot apply field access to the type Date.

[arnaudmanir]

Hello. Will someone please tell me what's wrong with my formula? 

= Table.TransformColumns( #"Changed Type",{"ADVANCE_DATE (YYYYMMDD)", each Text.Combine({Date.ToText([#"ADVANCE_DATE (YYYYMMDD)"], "yyyy"), Date.ToText([#"ADVANCE_DATE (YYYYMMDD)"], "MM"), Date.ToText([#"ADVANCE_DATE (YYYYMMDD)"], "dd")})}, null)

I'm getting this error message:

Expression.Error: We cannot apply field access to the type Date.
Details:
Value=5/4/2023
Key=ADVANCE_DATE (YYYYMMDD)

[m_dekorte · Accepted solution]

Hi @arnaudmanir,

Give this a go instead. You can copy the full code into a new blank query

let
    Source = Table.FromColumns(
        {{#date(2023, 5, 7), #date(2023, 10, 5)}},
        {"ADVANCE_DATE (YYYYMMDD)"}
    ),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ADVANCE_DATE (YYYYMMDD)", type date}}),
    Transform = Table.TransformColumns( #"Changed Type",
    {
        {"ADVANCE_DATE (YYYYMMDD)", each Text.Combine( 
            { 
                Date.ToText( _, [Format="yyyy"]), 
                Date.ToText( _, [Format="MM"]), 
                Date.ToText( _, [Format="dd"])
            }), type text
        }
    }
)
in
    Transform

Ps. If this helps solve your query please mark this post as Solution, thanks!

[arnaudmanir]

Thank you. Can you help me understand why [Format="dd" ] goes between square braces? 

[m_dekorte]

Hi @arnaudmanir,

You can provide options as a record, the record initializer is a set of [ ] (square brackets).

Review the M code function library for parameter specifics; here's a copy.

options, may be provided to specify additional properties. The record can contain the following fields:

• Format: A text value indicating the format to use. Omitting this field or providing null will result in parsing the date using a best effort.

• Culture: When Format is not null, Culture controls some format specifiers. For example, in "en-US" "MMM" is "Jan", "Feb", "Mar", ..., while in "ru-RU" "MMM" is "янв", "фев", "мар", .... When Format is null, Culture controls the default format to use. When Culture is null or omitted, Culture.Current is used.