Forum Discussion
Table.TransformColumns issue
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.
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 TeamIf 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
6 Replies
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?
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 TeamIf 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
how does the source data look like? Are the links in place?
Sorry not sure what you mean by links, the original data source type is datetime2(7)
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})
