Change a number to scientific format in Power query

[gsksarepta]

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]

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. 

[jbwtp]

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.

Kind regards,

John

[v-yanjiang-msft]

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:

DAX:

Create a calculated column:

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

Result:

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]

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