Forum Discussion

TomiKasurinen's avatar
TomiKasurinen
Frequent Visitor
5 years ago

Changing column types in advanced editor

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

Need Help

7 Replies

  • PC2790's avatar
    PC2790
    Community Champion
    5 years ago

    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's avatar
      TomiKasurinen
      Frequent Visitor
      5 years ago

      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's avatar
        PC2790
        Community Champion
        5 years ago

        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.