Table.TransformColumns issue

[sperry19]

Hi All,

I'm trying to fix up a report that stopped working for whatever reason, I do not have much experience with power query and am stuck. The problem is I get the error below when trying to transform the column into a date.

Here is the code where the error occurs.

in
    Source,
    #"Extracted Date" = Table.TransformColumns(CMSales,{{"CREATED_DATE", DateTime.Date, type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Extracted Date", each ([STATUS] <> "CANCELLED")),
    CREATED_DATE = #"Filtered Rows"{0}[CREATED_DATE]

Any advice appreciated.

[v-cgao-msft · Accepted solution]

Hi @sperry19 ,

Try to split the conversion into two steps.

    #"Changed Type" = Table.TransformColumnTypes(CMSales,{{"CREATED_DATE", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"CREATED_DATE", type date}})

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[ronrsnfld]

Depending on what your data really is like, something like the code below might work:

Table.TransformColumns(CMSales, {"CREATED_DATE", each DateTime.Date(DateTime.From(_)), type date})

[watkinnc]

Try using Date.From instead of DateTime.Date.

--Nate

[sperry19]

Hey Thanks for the suggestion. Unfortunatly this did not work. the original data type is datetime2(7) in the database. Should I be using some function to trim the last digits so it is returned as YYYY-MM-DD hh:mm:ss?

[qrasooli]

how does the source data look like? Are the links in place?

[sperry19]

Sorry not sure what you mean by links, the original data source type is datetime2(7)