cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rearles
Frequent Visitor

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

14 REPLIES 14
angai97
New Member

Hi Pete, 
I saw this thread and I have similar (not entirely the same) question as the above. I have a serial ID column field in my data set that has text and number ID numbers that goes something like "123.600S" or "123.6005". When I load it into power BI and change it into text format the decimal numbers will convert into something crazy like "123.60050000000001". Is there a way to prvent it from doing this? I need the ID to stay as "123.6005" in order to merge query via the column field.
Thanks!

Hi @angai97 ,

 

If your serial numbers always have the same number of characters, then you could use Text.Start([Serial Number], 8).

If it's more complicated than this, then you'll need to create a new thread in the Power Query forum with plenty of examples of the different possible serial number formats that need to be fixed.

 

Pete



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

Proud to be a Datanaut!




v-shex-msft
Community Support
Community Support

Hi @Rearles,

I think these should be the limit of the analysis tabular model data engine. In my opinion, I'd like to suggest you convert this field to text type with format function to keep the specific formats.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
JirkaZ
Solution Supplier
Solution Supplier

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.

Rearles
Frequent Visitor

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

JirkaZ
Solution Supplier
Solution Supplier

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.

Rearles
Frequent Visitor

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

BA_Pete
Super User
Super User

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

 

 



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

Proud to be a Datanaut!




Rearles
Frequent Visitor

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

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

 

 

This gives me the following output:

BA_Pete_0-1643639381808.png

 

Pete



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

Proud to be a Datanaut!




Rearles
Frequent Visitor

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



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

Proud to be a Datanaut!




Rearles
Frequent Visitor

Hi Pete,

 

Thanks again for your latest reply. I now have hit some complications as when I make mileage a text column (as opposed to a decimal) to allow the code to work, it changes the values to something crazy.

 

Rearles_0-1643647358500.png

I will try and find a way to stop it going weird like this, then I'll try that code you sent me again.

 

Cheers, Rob

 

 

Hi @Rearles ,

 

Sorry for delayed reply.

You shouldn't need to make the field text for this to work. You'll see in my code the bit like this: Text.From([Mileage]) which converts it to text within the calculation.

 

Pete



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

Proud to be a Datanaut!




Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors