Forum Discussion

arnaudmanir's avatar
arnaudmanir
Frequent Visitor
3 years ago
Solved

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

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)

Need Help
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    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!

3 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    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's avatar
      arnaudmanir
      Frequent Visitor
      3 years ago

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

      • m_dekorte's avatar
        m_dekorte
        Resident Rockstar
        3 years ago

        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.