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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
gsksarepta
Helper II
Helper II

Change a number to scientific format in Power query

How can I change a large number to Scientific format in Power Bi - Power Query Editor? I am aware of the option to do this on data mode but I have a Pivot Table that contains many variables of different decimal numbers into one column for charting and it is set to Auto format in table mode. Attached a screenshot of my column of values in the Pivot table that combines the data of many variables. I only want to see the large numbers in Scientific here. Thank you.

 

gsksarepta_0-1666040498397.png

 

4 REPLIES 4
gsksarepta
Helper II
Helper II

Hi, Thank you @v-yanjiang-msft @jbwtp both for responding here. I tried the DAX formula but it converted the data to text which defeated my purpose. Maybe I should have asked in different way. Below is a line chart and I need the Y axis to be displayed in Scientific format when the numbers are larger than 100,000. As mentioned in the picture post, the Y axis column has a mix of numbers from very small to very large. And I am selecting the Y axis based on the slicer. 

gsksarepta_0-1666106226822.png

 

Hi @gsksarepta,

 

I don't think that the standard line chart is that flexible. The axis can be either number format or sceintific format (depending on the column format set in the "Column Tools"), but not the mix of this. Unless, the functionality exists in some custom/3rd party visuals.

 

jbwtp_0-1666128073740.png

 

Kind regards,

John

 

 

v-yanjiang-msft
Community Support
Community Support

Hi @gsksarepta ,

According to your description, here's my solution.

Power Query:

Add a custom column.

if Text.Length(Text.From([Column1]))>11 and Text.Length(Text.From([Column1]))<16  then Currency.From([Column1]) else [Column1]

Result:

vkalyjmsft_0-1666062714646.png

DAX:

Create a calculated column:

Column =
IF (
    LEN ( 'Table'[Column1] ) > 11,
    FORMAT ( 'Table'[Column1], "Scientific" ),
    FORMAT ( 'Table'[Column1], "General Number" )
)

Result:

vkalyjmsft_1-1666062844436.png

Note: After hard foramt, the column type will be text instead of number.

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jbwtp
Memorable Member
Memorable Member

Hi @gsksarepta,

 

Can I ask a question: why are you bothered?

The sceintific fornmat in this case is only a way to visualise the number, it does not change it's qualities (in the way for example Int64.Type) does. Your end user presumably won't see them in the query view anyway.

If you really like to see them formatted, you can always convert them to text [ Number.ToText(_, "e") ], but at the cost of actually making them text type.

 

Cheers,

John

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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