Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PBI_Rocks
Frequent Visitor

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

 

 

 

 

1 ACCEPTED SOLUTION

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]

 

 

PBI_Rocks_0-1661855149454.png

 

 

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

 

 

View solution in original post

8 REPLIES 8
BA_Pete
Super User
Super User

Hi @PBI_Rocks ,

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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 @PBI_Rocks ,

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi BA_Pete,

I think that this article explains the requirement for the Fixed Decimal rather than Floating Point better than I can.

 

Choosing Numeric Data Types in DAX - SQLBI

 

Regards,

B

Hi @PBI_Rocks ,

 

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:

BA_Pete_0-1661846515721.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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]

 

 

PBI_Rocks_0-1661855149454.png

 

 

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 bug feature.

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors