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.
Hello All,
I am loading multiple csv files using power query.
The CSV files have various columns and a few of the columns have numbers like 72.00 or 86.00.
When I load them to powerBI then the program identifies them as 7200 and 8600.
I use English (United Kingdom) as local settings.
Is there any way to successfully convert the decimal numbers to integers? Im not interested in having the decimals in the table.
A function that can ignore system locale would be ideal.
Query im using to load data is the following (it is not the whole query):
= (MkDailyCallsForAllSourceFile_template02 as any) => let // use the template parameters 1 or 2 to check behaviour for each template Source = Csv.Document(File.Contents(MkDailyCallsForAllSourceFile_template02),[Delimiter=",", Columns=11, Encoding=1253, QuoteStyle=QuoteStyle.Csv]), #"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}}), #"Text to Number" = Table.TransformColumnTypes(#"Changed Type",{{"InAnswered", Int64.Type}, {"InNonAnswered", Int64.Type}, {"OutAnswered", Int64.Type}, {"OutNonAnswered", Int64.Type}, {"TotalAnswered", Int64.Type}, {"TotalNonAnswered", Int64.Type}})
Hi @themistoklis,
I have made a test with creating a csv sample and convert the dcimal numbers to whole number, everything works as expected. I also change the locale type to UK.
You could have a test with the sample file attached below.
If it is convenient, could you share a dummy pbix file which can reproduce the issue, so that I could have a test on it in my side? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
Best Regards,
Cherry
Hi @v-piga-msft
I used your file and it works ok but i still have issues with my PowerBi workspace.
I have put in the dropbox link below, the workspace and a folder with the csv files that i load to the report.
When you go to the report you will notice that on the first, second and third month the values displayed are very hight (200k +)
On the other months the maximum value is 2-3K... and it is the correct one.
If you open the csv files from the first month with notepad++ you will see that there are decimal places there. For some reason powerBI doesnt take into account these decimal places.
Is there any function that i can use which ignores the PC's locale and i can force it to use the values as integers?
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 |
---|---|
114 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |