Forum Discussion
SQL Server Datatype -> Power Query Fixed decimal number ($)
Hi,
Is there a SQL datatype that is recognised by PQ as Fixed decimal number.
Decimal(19,4) , MONEY and SMALLMONEY are all recognised as a Decimal number.
The column can be converted using Table.TransformColumnTypes(TestTable,{{"DecimalDef", Currency.Type}}) but this appears to 'break' folding.
Other than placing the conversion transform as the last step, Have I missed something obvious?
Thanks.
Regards,
B
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
8 Replies
Hi Anonymous ,
The fact that converting to Currency.Type in PQ breaks folding tends to suggest that there isn't a direct equivalent that can pass from SQL to PQ.
Why do you want to do this in PQ anyway? I, personally, always see Currency/Fixed Decimal as formatting rather than data type changes and thus always make these changes in the front end.
Pete
Hi Pete,
I agree - for the reason you gave - that it is possibly the case that there isn't a direct equivalent that can pass from SQL to PQ as currency datatype.
The datatype conversion needs to be done, because the rounding issues of Fixed Decimal are not acceptable to our users, so the Currency datatype is implemented.Regards,
B
Hi Anonymous ,
If it's rounding of the values that's the problem, then you can use the Number.Round~ family of functions in Power Query. There's a few different types you can use, and each have different arguments you can use to get exactly the behaviour you need:
https://docs.microsoft.com/en-us/powerquery-m/number-round
I believe all of these functions will fold to SQL Server.
Pete
