Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
at the first look my topic could seems similar to many others but I didn't find any solution to my problem among the dozens of topics I've read.
Let's explain, I'm beginning to use power BI to visualize some data from an API and a query in particular is problematic.
Indeed, it returns me some numeric datas in a text format. Problem is that I need this columns to have numeric type to be able to do some curves in Power view.
Here is what I tried:
- Change data type from text to Decimal number using the Data type button in power BI and in power query editor --> Gives me an error saying I can't convert to a number.
- Create a new column and convert the text numbers with VALUE(ColumnToConvert) and CONVERT(ColumnToConvert, number) --> Same error
- Change the type in the advanced editor with Table.TransformColumnTypes(#"Column1 développé",{{"Column1.value", type number}}) --> I also tried int64.Type instead of number but it doesn't work...
If someone has other ideas I'd be very glad to ear it and try.
Thanks in advance and sorry for my bad english spoken,
Cado
Solved! Go to Solution.
So I just had a similar issue @Cado_one and the problem turned out to be a special character. I had tried using Trim and Clean and a bunch of other steps like replacing line feeds, etc. Anyway, I found it by clicking on the Error in Power Query, copying the value, pasting into Notepad and then copying out of Notepad and into a Replace Value step in my Power Query. Here is some of the code:
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Kudos", Text.Clean, type text}}),
#"Removed Duplicates" = Table.Distinct(#"Cleaned Text", {"Title"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Duplicates"," ","",Replacer.ReplaceText,{"Kudos"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(tab)","",Replacer.ReplaceText,{"Kudos"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","#(00A0)","",Replacer.ReplaceText,{"Kudos"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","#(cr)","",Replacer.ReplaceText,{"Kudos"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","#(lf)","",Replacer.ReplaceText,{"Kudos"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","#(cr)#(lf)","",Replacer.ReplaceText,{"Kudos"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","","",Replacer.ReplaceText,{"Kudos"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value6",{{"Kudos", Int64.Type}})
So I just had a similar issue @Cado_one and the problem turned out to be a special character. I had tried using Trim and Clean and a bunch of other steps like replacing line feeds, etc. Anyway, I found it by clicking on the Error in Power Query, copying the value, pasting into Notepad and then copying out of Notepad and into a Replace Value step in my Power Query. Here is some of the code:
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Kudos", Text.Clean, type text}}),
#"Removed Duplicates" = Table.Distinct(#"Cleaned Text", {"Title"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Duplicates"," ","",Replacer.ReplaceText,{"Kudos"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(tab)","",Replacer.ReplaceText,{"Kudos"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","#(00A0)","",Replacer.ReplaceText,{"Kudos"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","#(cr)","",Replacer.ReplaceText,{"Kudos"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","#(lf)","",Replacer.ReplaceText,{"Kudos"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","#(cr)#(lf)","",Replacer.ReplaceText,{"Kudos"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","","",Replacer.ReplaceText,{"Kudos"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value6",{{"Kudos", Int64.Type}})
You were right, I did a replace and the conversion worked !
What a weird story 😌
Thanks to both of us @VSrujana @Greg_Deckler
Hi @Cado_one ,
Please check if you can view the error in detail , in the query errors folder.
Usually , the possible reasons that text cannot convert to number are :
1. #N/A values in the text ,
2. Alphanumeric values
3. Any other special characters
Please check for these flags in the text columns before conversion to numbers
Thanks
Hi @VSrujana
All the numbers in the column have a normal appearance like 96384556017.0302 and nothing more
This is a screenshot of the error appearing below the table when trying to convert
is there a location where I could have more details about the error ?
Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |