cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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:

 

Rearles_0-1643624840525.png

 

Rearles_2-1643625059382.png

 

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:

 

Rearles_1-1643625015233.png

 

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

12 REPLIES 12
Syndicate_Admin
Administrator
Administrator

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.

 

Pete

 

 

Syndicate_Admin
Administrator
Administrator

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:

let
    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(
    Text.BeforeDelimiter(
        Text.From([Mileage]),
        "."
    )
)),
    addYards = Table.AddColumn(addMiles, "Yards", each Number.From(
    Text.PadEnd(
        Text.AfterDelimiter(Text.From([Mileage]),"."),
        4, "0"
    )
))
in
    addYards

 

Pete

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:

 

Rearles_0-1643646627302.png

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

 

Cheers, Rob

@Rearles 

 

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

Number.From(
    Text.PadEnd(
        Text.AfterDelimiter(Text.From([Mileage]),"."),
        4, "0"
    )
)

 

Pete

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Kudoed Authors
Users online (3,409)