Number with $ after import to PBI how to change to whole number ?

[admin11]

Hi All

My field name = Amount , i am unable to change to number. can some one share with me how to go around ?

My CSV file 

https://www.dropbox.com/s/wr1x6w2bjgmaxtj/CRM_TS_10.csv?dl=0

Paul

[v-robertq-msft · Accepted solution]

Hi, @admin11 

After testing with your sample data, I found that the true reason that you can’t change the data type to “Number” is that the [Amount] contains “$” in the original data, and it leads to the value becomes “Text” type, I  think you can replace the “$” with blank value first in the Power Query first and add the “$” symbol after it’s imported into the Power BI, you can try my steps:

1. Click on the [Amount] field and replace values like this:

2. Right click the [Amount] column and change type:

3. Click “Close and Apply”, then add the “$” symbol like this:

This is the M-code in my Advanced editor, you can copy and change the data source or just follow my steps above to achieve this:

let
    Source = Csv.Document(File.Contents("D:\ChromeCoreDownloads\CRM_TS_10.csv"),[Delimiter=",", Columns=15, Encoding=936, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"CRM_TS :", type text}, {"", type text}, {"_1", type text}, {"_2", type text}, {"_3", type text}, {"_4", type text}, {"_5", type text}, {"_6", type text}, {"_7", type text}, {"_8", type text}, {"_9", type text}, {"_10", type text}, {"_11", type text}, {"_12", type text}, {"_13", type text}}),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"POTENTIALID", type text}, {"Closing Date", type date}, {"Deal Name", type text}, {"Stage", type text}, {"Company Name", type text}, {"Full Name", type text}, {"Amount", type text}, {"Deal Owner", type text}, {"Main Segment", type text}, {"Sub Segment", type text}, {"Email", type text}, {"Mobile", type text}, {"Department", type text}, {"Title", type text}, {"SBU", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type2","$","",Replacer.ReplaceText,{"Amount"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value",{{"Amount", Currency.Type}})
in
    #"Changed Type3"

And you can get what you want.

You can download my test pbix file here

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[Paulyeo11]

@v-robertq-msft 

Thank you very much , it work fine now.

Appreciate very much for your help.

Paul

[amitchandak]

@admin11 , I have loaded the CSV, Removed the first row, and then made the next row as header and I got the amount as the number

let
    Source = Csv.Document(File.Contents("C:\Users\Amit.Chandak\Downloads\CRM_TS_10.csv"),[Delimiter=",", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"POTENTIALID", type text}, {"Closing Date", type date}, {"Deal Name", type text}, {"Stage", type text}, {"Company Name", type text}, {"Full Name", type text}, {"Amount", Currency.Type}, {"Deal Owner", type text}, {"Main Segment", type text}, {"Sub Segment", type text}, {"Email", type text}, {"Mobile", type text}, {"Department", type text}, {"Title", type text}, {"SBU", type text}})
in
    #"Changed Type1"