Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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:
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.
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:
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.
@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"
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |