Forum Discussion

jonoling's avatar
jonoling
Frequent Visitor
5 years ago
Solved

SQL Tables importing without associated data types

Hi,   I have a fact table that I'm connecting to in SQL. When reviewing the INFORMATION_SCHEMA.COLUMNS query in SSMS for the table it shows the [order_date] field as being stored as a datetime data...
Need Help
  • jonoling's avatar
    jonoling
    5 years ago

    None, it's the out of the box SQL Server connector from Power Query. The only inputs it's taking is the SQL string and DB, then navigating to the table in question. 

     

    let
       Source = Sql.Database(sqlserver, db)
       Table = Source{[Schema="dbo",Item=tablename]}[Data]
    in
       Table

     Agree, it doesn't seem to be a widespread issue, this is the first time I've come across an issue like this. Just tested with a sandbox WorldWideImporters DB and the native query generated brings through datatypes fine. 

     

    Odd!

UThomas's avatar
UThomas
New Member
5 years ago

Hello, I have the same issue with a MS SQL Database. There are a custom data type called (APdate) which is a datetime-Type, but PowerQuery didn't recognize it as datetime but set it to text.

 

I use the standard "From MS SQL Database"-Connector.

let
    Quelle = Sql.Databases("SQL1.xxx.de"),
    xxx = Quelle{[Name="xxx"]}[Data],
    dbo_yyy = xxx{[Schema="dbo",Item="LIEFRECHNUNG"]}[Data]
in
    dbo_LIEFRECHNUNG
select [$Table].[id] as [id],
    [$Table].[timestamp] as [timestamp],
    [$Table].[LIEFRECHNUNG] as [LIEFRECHNUNG],
    [$Table].[STATUS] as [STATUS],
    [$Table].[ADRESSE] as [ADRESSE],
    [$Table].[PERSONAL] as [PERSONAL],
    convert(nvarchar(max), [$Table].[RECHNUNGSDATUM]) as [RECHNUNGSDATUM],
    [$Table].[RECHNUNGSNR] as [RECHNUNGSNR],
    convert(nvarchar(max), [$Table].[DATUM]) as [DATUM],

 

if I change the M-code to:

 

let
    Quelle = Sql.Database("SQL1.xxx.de", "yyy", [Query="SELECT * from LIEFRECHNUNG"])
in
    Quelle

the columns typ is correct (datetime).

 

What's wrong with the first (out-of-the-box) M-Code?

Why isn't PowerQuery using the system data type from SQL-Server?