cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
gsksarepta
Helper I
Helper I

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 I
Helper I

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors