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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
admin11
Memorable Member
Memorable Member

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

Hi All

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

admin11_0-1613726178552.png

My CSV file 

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

 

Paul

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1613983414641.png

 

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

v-robertq-msft_1-1613983414649.png

 

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

v-robertq-msft_2-1613983414658.png

 

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.

View solution in original post

3 REPLIES 3
v-robertq-msft
Community Support
Community Support

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:

v-robertq-msft_0-1613983414641.png

 

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

v-robertq-msft_1-1613983414649.png

 

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

v-robertq-msft_2-1613983414658.png

 

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.

@v-robertq-msft 

Thank you very much , it work fine now.

Appreciate very much for your help.

Paul

amitchandak
Super User
Super User

@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"

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.