Changing column types in advanced editor

[TomiKasurinen]

Hello,

The query worked well before the Pervasive DB was updated. And now I get this error:

DataSource.Error: ODBC: ERROR [22007] [Pervasive][ODBC Client Interface]Invalid date, time or timestamp value.

Is it possible to convert the date column types to text in advanced editor? Or will this even solve this problem? I'm not very familiar with the "M" functions.

This is the current code in advanced editor

let
Source = Odbc.DataSource("dsn=*****", [HierarchicalNavigation=true]),
*****_Database = Source{[Name="*****",Kind="Database"]}[Data],
Sales_Table = *****_Database{[Name="Sales",Kind="Table"]}[Data]
in
Sales_Table

How can I force all column types to text? Or if I have columns named CreateDate and DeliveryDate how can I change those to text? I've tried to read these articles https://docs.microsoft.com/en-us/powerquery-m/type-conversion but the bulb hasn't really lightened yet.

BR,

Tomi

[PC2790]

Hi @TomiKasurinen ,

If your requirement here is onlt to convert date type column to text, here is the corresponding M code:

= Table.TransformColumnTypes("YourTable",{{"CreateDate", type text}})

If you want to avoid the coding part, you can also do it using the user interface as below:

[TomiKasurinen]

This might be a stupid question but where do I have to put this code

= Table.TransformColumnTypes("YourTable",{{"CreateDate", type text}})

in the current M code?

let
Source = Odbc.DataSource("dsn=*****", [HierarchicalNavigation=true]),
*****_Database = Source{[Name="*****",Kind="Database"]}[Data],
Sales_Table = *****_Database{[Name="Sales",Kind="Table"]}[Data]
in
Sales_Table

And if I want to change multiple column types how do I modify the code?

The occurring error prevents the table from loading so the user interface isn't usable yet.

[PC2790]

Yes, the amendment would be in the current M code. The code would look like:

let
Source = Odbc.DataSource("dsn=*****", [HierarchicalNavigation=true]),
*****_Database = Source{[Name="*****",Kind="Database"]}[Data],
Sales_Table = *****_Database{[Name="Sales",Kind="Table"]}[Data],
#TransformedCreatedDate= Table.TransformColumnTypes("Sales_Table",{{"CreateDate", type text}}),
#TransformedXYZColumn= Table.TransformColumnTypes("#TransformedCreatedDate",{{"XYZColumn", type text}}),
#TransformedABCColumn= Table.TransformColumnTypes("#TransformedXYZColumn",{{"ABCColumn", type text}})
in
#TransformedABCColumn

Note: Assumption is that there are three columns-CreateDate,XYZColumn and ABCColumn for which you want to change the datatype to text.

[TomiKasurinen]

This generated a new error which roughly translates to 

Expression.Error: Value "Sales_table" can not be changed into a type Table.

Im assuming that the error in table loading is preventing this code to work?

[PC2790]

Looking at your initial error, it seems to be a data issue: See articles:

https://www.tek-tips.com/viewthread.cfm?qid=1154380

SQL Error [22007]: THE DATE, TIME, OR TIMESTAMP VALUE IS INVALID. SQLCODE=-180, SQLSTATE=22007, DRIV...

and check for your error code-22007 here:

https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/appendix-a-odbc-error-codes?view=sql-...

This could be caused due to bad data coming from source

[TomiKasurinen]

Thank you for the replies so far!

I still haven't figured out how to solve this issue. There is no SQL statements in the ODBC connector.

Is it not possible to get the raw data to Power Query and filter the errors/dates causing the errors there?

[TomiKasurinen]

I kind of solved this.

I used the SQL expression in ODBC connector to select the columns I needed from the database.

Then I wanted to try to select all of the date/time columns from this table one by one to see which column was causing the initial error. Not a single error with any of the existing date/time columns. So this is kinda weird, there may be something shady with the current version of the database and Power BI or ODBC drivers?