Forum Discussion
SQL Server Datatype -> Power Query Fixed decimal number ($)
Hi BA_Pete ,
It appears that the Value.NativeQuery does not work as I expected.
Created and populated a small test table.
CREATE TABLE [ref].[TestTable](
[MoneyDEF] [MONEY] NOT NULL,
[SmallMoneyDef] [SMALLMONEY] NOT NULL,
[DecimalDef] [DECIMAL](19, 4) NOT NULL,
[NumericDef] [NUMERIC](19, 4) NOT NULL,
[FloatDef] [FLOAT] NOT NULL
) ON [PRIMARY]
The following M gives the desired results in that the MONEY/SMALLMONEY columns are imported FIXED DECIMAL the others as FLOAT
let
Source = Sql.Database("myServer", "myDatabase"),
ref_TestTable = Source{[Schema="ref",Item="TestTable"]}[Data]
in
ref_TestTable
I have been using Value.NativeQuery, which for some reason - as yet to be determined - does not import the MONEY/SMALLMONEY as FIXED DECIMAL all columns are FLOAT
let
Source = Sql.Databases(Server),
db = Source{[Name = Database]}[Data],
TestTable = Value.NativeQuery(
db,
"SELECT
[MoneyDEF],
[SmallMoneyDef],
[DecimalDef],
[NumericDef]
FROM [ref].[TestTable]",
null,
[EnableFolding = true]
)
in
TestTable
The first workaround is not to use Value.NativeQuery but this outcome is a little surprising to me.
Will try and look more into Value.NativeQuery.
Regards,
B
Hi Anonymous ,
Thanks for sharing the article, this makes sense.
Based on my own tests, both CASTing and CONVERTing numerical values to the SQL MONEY data type on SQL Server imports into Power Query correctly as the PQ Fixed Decimal data type:
As such, I think there's two possible issues:
1) PQ is applying an automatic data type evaluation on import, for some reason (SQL Server is classified as a 'Structured Source', so this should never happen, but just in case). You would see this as an auto-generated 'Changed Types' step under your Source/Navigation steps and this should be deleted to revert back to the source data types.
2) Your source data isn't being held as MONEY type in the database. This, as above, can be corrected using either CAST or CONVERT on these fields in the DB. However, the question would remain as to whether you are actually getting the data accuracy/integrity that you desire, when the values aren't even being stored in the DB as this data type.
Pete
Hi BA_Pete ,
It appears that the Value.NativeQuery does not work as I expected.
Created and populated a small test table.
CREATE TABLE [ref].[TestTable](
[MoneyDEF] [MONEY] NOT NULL,
[SmallMoneyDef] [SMALLMONEY] NOT NULL,
[DecimalDef] [DECIMAL](19, 4) NOT NULL,
[NumericDef] [NUMERIC](19, 4) NOT NULL,
[FloatDef] [FLOAT] NOT NULL
) ON [PRIMARY]
The following M gives the desired results in that the MONEY/SMALLMONEY columns are imported FIXED DECIMAL the others as FLOAT
let
Source = Sql.Database("myServer", "myDatabase"),
ref_TestTable = Source{[Schema="ref",Item="TestTable"]}[Data]
in
ref_TestTable
I have been using Value.NativeQuery, which for some reason - as yet to be determined - does not import the MONEY/SMALLMONEY as FIXED DECIMAL all columns are FLOAT
let
Source = Sql.Databases(Server),
db = Source{[Name = Database]}[Data],
TestTable = Value.NativeQuery(
db,
"SELECT
[MoneyDEF],
[SmallMoneyDef],
[DecimalDef],
[NumericDef]
FROM [ref].[TestTable]",
null,
[EnableFolding = true]
)
in
TestTable
The first workaround is not to use Value.NativeQuery but this outcome is a little surprising to me.
Will try and look more into Value.NativeQuery.
Regards,
B
That's interesting, I wasn't aware of this
bugfeature.Personally, I never use native queries, so have never come across this. If you're able to get views created on the DB using your native query script, that would always be my strong recommendation for many reasons, this one now added to the list!
I suppose you could add the CONVERT/CAST functions into your native query, would be interesting to see if that works.
If you get the time, it would be great if you could update this thread with your findings. I'm sure it would help future users.
Pete
Hi BA_Pete ,
The same result occurs with VALUE.NATIVEQUERY on views created on the test table.
If there are futher devlopments I will update this thread.
Thanks for your help.
Regards,
B