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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
v-cgao-msft
Community Support
Community Support

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
v-cgao-msft
Community Support
Community Support

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})
watkinnc
Super User
Super User

Try using Date.From instead of DateTime.Date.

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors