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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors