Power Query - Problem with Decimal Conversion

[themistoklis]

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}})

[v-piga-msft]

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

[themistoklis]

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.

Dropbox

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