Forum Discussion

KenMann's avatar
KenMann
Regular Visitor
2 years ago
Solved

Can we change column type in Fabric SQL Endpoint ?

Hi guys,

 

I have CSV file that I uploaded into my Lakehouse, soon after by using drag and drop to Tables, I would have 1 table created with the same structure as the CSV. It is actually a CSV contain Date table. However when I take a look at the table structure, the column has no Date type column even with the field contain date.

 

Can we change it ? or can we add new column based on the exisiting column and lets say I want to new column to have Date type.

Firstly I though Lakehouse SQL Endpoint is the best place to do all that, but turns out I cannot find any button or function to support my plan.

Any other place I can do that ? or how to have a column with Date type when we have table with source is CSV ?

 

Thanks

 

 

 

 

Need Help
Show and Tell
  • KenMann's avatar
    KenMann
    2 years ago

    Hi,

     

    I'm finally manage to make the column as Date. Figure it out that I better not using "Drag and Drop" on the csv file to my Lakehouse table. Instead doing the Get Data feature so I can manually change the column inside PowerQuery, before the table created.

     

    Issue solved. Thanks.

6 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    2 years ago

    Hi KenMann 


    Because a CSV is just a text file, when creating a table in the Lakehouse it cannot determine what the column data types are.


    One way to do it when querying via the SQL End Point is you can use the following code to cast it on the fly as shown below.

     

    Select 
CAST(FullDateAlternateKey as Date) as Date
from dbo.MyTableName

     

    NOTE: The SQL Endpoint is case sensitive for your column names so make sure to spell them exactly the same. 

    • KenMann's avatar
      KenMann
      Regular Visitor
      2 years ago

      Hi,

      Yes, this what I'm thinking as well. But may I know how to do it in SQL Endpoint ? 

      Are you referring to that button "New sql query" and write the CAST over there like this ?

      But, as this is just a query, how this will alter the table it self which then can be used in my data set outside (Power BI Dekstop)

       

      Thanks,

       

      • GilbertQ's avatar
        GilbertQ
        Super User
        2 years ago

        Hi KenMann 

         

        When you use the Power Query data source from "SQL Server" you can insert a query, which you can use from your screenshot above, which will then bring the data in as Date.