cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
irnm8dn Member
Member

Scientific Format in Dataset .csv - PLEASE HELP!

I have a source file that is in a .csv format.  One of the columns is presenting data in a scientific format.  Regardless of trying to change format in the .csv, Power BI wants to convert the records.  Additionally, the .csv once saved and reopened, also reverts.

 

Is there a way in Power BI to interpret this columns as Text or Number and have the file interpret the values properly as they come from the source?

 

Thanks.

 

BTW, have tried all the recommended tricks in both Excel and in the community.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Scientific Format in Dataset .csv - PLEASE HELP!

Perhaps

 

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\science.csv"),[Delimiter=",", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column", type text}})
in
    #"Changed Type"

?

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


6 REPLIES 6
Super User
Super User

Re: Scientific Format in Dataset .csv - PLEASE HELP!

What does your data look like, something like:

 

1e+13

 

?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


irnm8dn Member
Member

Re: Scientific Format in Dataset .csv - PLEASE HELP!

@Greg_Deckler

 

Example from the .csv is:

 

1.38206E+11
1.38206E+11
1.38206E+11
1.38233E+11

 

Upon import in Power BI it looks like:

 

Capture.JPG

 

Additionally, the problem is that the conversion to Scientific and then ultimately the Power BI interpretation is changing the values received from the source.  (And in some cases taking these unique values and making them look like dupes...)

 

Thanks.

 

Super User
Super User

Re: Scientific Format in Dataset .csv - PLEASE HELP!

Perhaps

 

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\science.csv"),[Delimiter=",", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column", type text}})
in
    #"Changed Type"

?

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


irnm8dn Member
Member

Re: Scientific Format in Dataset .csv - PLEASE HELP!

@Greg_Deckler

 

Can you step me through how to do this.  I am still a bit of a newb.

 

Where, how, etc. the statement below (or one like it) neeeds to be applied would help a great deal.

 

Thanks.

Super User
Super User

Re: Scientific Format in Dataset .csv - PLEASE HELP!

So, what I did was to replace the default Change Type step in the query. So, I selected that step in the query editor and then I right-click the column and change the type to text and it asks if I want to replace the current step, I say yes.

 

Could be more specific if you could paste your query code from the Advanced Editor.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


irnm8dn Member
Member

Re: Scientific Format in Dataset .csv - PLEASE HELP!

@Greg_Deckler

 

Thanks, this seemed to move me incrementally forward.  What I don't understand is once the relationship is made, why my table is not showing me a correct output, by the desired dimension?

 

The left most column should be a list of Customers.

 

Capture.JPG