Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
sperry19
New Member

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.

sperry19_0-1680816501604.png

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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
Super User
Super User

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})
Anonymous
Not applicable

Try using Date.From instead of DateTime.Date.

 

--Nate

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
Frequent Visitor

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)

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.