Showing results for 
Search instead for 
Did you mean: 

changing number of decimal places on query editor

Hi there,


I have a column of data which needs to be shown to 4 decimal places. I have tried to set this up by changing the data type to decimal and changing degree of accuracy to 4 decimal places and taking it off auto. See below:






However, when I start to use query editor the column reverts back to being rounded where the decimal ends in a 0. E.g. in the example photo above and below, a cell will read '0.0220', but in query editor it will turn into '0.022' and get rid of the 0 at the end.


See below in query editor:




Is there any way to input a command in query editor that will make sure all the numbers are shown to 4 decimal places?


Effectively the 4 digits after the . refer to yards, so of course when i extract the yards beyond this step with after text delimeters etc. if its coming out as 22 yards instead of 220 yards it isn't ideal.


I have tried changing the data type in query editor to fixed decimal but that only puts them to 2 d.p and not 4. Changing to text also hasn't worked. Any advice would be greatly appreciated!


Cheers, Rob


Hi @Rearles ,


There's a few things to unpack here:


1) PQ isn't going to hold your trailing zero when using a number type. The trailing zero is effectively a preferred format, not a data type. PQ only works in specific data types.


2) As above, converting to text in PQ isn't going to help you once the values have been evaluated as number types. The trailing zero doesn't exist any more so can't be automagically displayed when changing the data type.


3) You may need to look into exactly how your source data is stored/formatted. It seems likely that these last four digits are NOT yards, they are decimal fractions of a mile.


Your immediate options:


a) If your source data is just poorly formatted, and the decimal places are indeed yards, then multiply your [Mileage] field in PQ by 10,000.


b) If your source data is correctly formatted, and the decimal places are fractions of miles, then mutiply your [Mileage] field by the appropriate conversion rate ([Mileage] * 1,760) to get a yards ouput.






Wait - it's not changing the value at all. It's just removing the trailing zero to allow for better compression (I assume). So if you're getting 22 yards instead of 220, then your subsequent logic is wrong.

Hi Pete,


many thanks for your response. I work in the railway industry and the way certain points on the line are identified are in this format where miles are shown before the "." and yards are shown after the "." So 0.022 and 0.0220 mean very different things to us (0.022 would be written as 0.0022 in any case). I know the programme is seeing 0.0220 and trying to be helpful by getting rid of the trailing 0, thinking it is a decimal fraction as opposed to an actual yards value.


Thank you for your suggestions though I will try these and let you know if anything works.


Cheers, Rob

yeah I know it is getting rid of the trailing 0 thinking it is a decimal fraction, although I explained in my reply to Pete that this annoyingly is just the way miles and yards are displayed in the industry i work in.


Cheers, Rob

Well in that case you can convert to text, split by delimiter to get miles and yards separately and then for the yards column you can use Text.PadEnd to get to the correct "decimal" numbers count.

Thanks very much for the idea, I'll give it a go and let you know if i get anywhere! 🙂

Hi @Rearles ,


That's a bit annoying isn't it! 🙂


Paste this over the default code of a new blank query and see if this works for you:

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcrJCcBADEPRXnwORjLxLLWY6b+NiHFu7wtVGRzksPOULXE2tUbgkqHI9cerz55tB5CXkfJobyeh9/kA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Mileage = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Mileage", type number}}),
    addMiles = Table.AddColumn(chgTypes, "Miles", each Number.From(
    addYards = Table.AddColumn(addMiles, "Yards", each Number.From(
        4, "0"



wow! that does work, I get the same table as an output you do.


Just thinking to how I can adapt my code to work for my original data as a source (I would share but i can't due to confidentiality agreement), which was the part of that code which put the yards output as the correct value? Will see if i can use that command and add to my existing Text.AfterDelimiter command I have:



is it something to do with the ,4, "0" you added after in yours?


Cheers, Rob



This is what you need to enter as the column calculation for the [Yards] column:

        4, "0"



Helpful resources

Ignite 2022


Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Top Kudoed Authors
Users online (3,829)