Changing Text to fixed decimal / Changing Text to Date gives me an Error

[Dklein]

Hello All,

    I am new to Power BI and I am having issues with 2 columns in my exported data from smartsheet. 

1. Dates: In my smartsheet, the date column is listed as "Date" but in Power BI it is listed as Text. This is stopping me from a proper Hieracrchy. The system does not let me do a full breakdown ( Month / Year / Quarter / Day).  When trying to convert the column from Text to Date, it tells me it cannot convert automatically. 

2. Grand Total: I am trying to convert my grand total column from a Text file to a Fixed decimal number. At the moment i am unable to get a "SUM" and only a "Count" due to the way it is stored in Power BI (Text). All has been adjusted in the smartsheet and even when i refresh the Data, it will not work and gives me the same pop up of " This cannot be automatically converted. 

This is slowing me down as i cannot create the reports I want and I need to keep using this smartsheet as it has 4000+ lines of claims history. 

Please let me know if anyone can help!

Best,
Daniel 

[Dklein]

I was able to fix it by the transform data tab. All is working now.

Thank you very much, I really appreciate it. 

Best,
Daniel 

[HotChilli · Accepted solution]

No apology required.

The best place to solve these issues is Power Query.  So, either 'edit' the data at connection time or use the Transform Data on the Home ribbon.

I can make educated guesses about what's going on but you can find out the exact issues. If there is a 'Changed Type' step and it throws errors then focus your attention on that.  If you have to change to the desired data type, select the column and use the ribbon to change/detect data type.  It will throw errors if there are problems.

Here's a couple of links:

https://learn.microsoft.com/en-us/power-query/dealing-with-errors#data-type-conversion-errors 

This page has almost everything on it but start here:

https://learn.microsoft.com/en-us/power-query/data-types#data-type-detection 

let me know how you get on and I will help you further.

[Dklein]

The Error Message is what is shown for both issues. I am sorry if I did not give all the information required. Please let me know if more info is required. 

Best, 

[HotChilli]

Show us your sample data.  There is a reason why they can't be converted automatically (in Power Query) so if you want help, you'll have to investigate those columns and show us.