Decimal point data

[FilipeBarroso]

Hello Community,

I have now gone 2 whole weeks without managing to fix the problem which is driving me crazy. 

I have had 2 csv origin data that were giving me 2 tables with 1 column of quantities each. These quantities were being given in whole number and there wasn´t any problem.

There was a change in the origin of the data which is now giving me this columns in decimal numbers. As you can see in the picture below, I downloaded the csv link to notepad+ and excel, and we can see that this column is in decimal format. 

To try to resolve the issue, I opened the power query and tried passing the respective columns to decimal point but didn´t work. 

Making the change in advanced editor from whole number (Int64.Type) to decimal (type number) also doesn´t work.

The table in power query is giving me a lot of errors as you can see in the picture below.

Many thanks,

FB

[BA_Pete]

Hi Filipe,

Can you do a couple of things please?

1) Click on the whitespace (NOT the word) in one of the error cells in your last screenshot, then screenshot the yellow error description that should show up at the bottom of the query window please.

2) From the query with all the errors, go to the Home tab > Advanced Editor. Copy ALL of the code from there and paste it into a code window ( </> button) here please. Make sure to X out any sensitive file/server paths in the Source step.

Pete

[FilipeBarroso]

Hello Pete,

Thanks for you answer.

Regarding 1), when clicking on whitespace (NOT the word) in one of the error cells:

Regarding 2), here follows the code of the CSV

<

let
Origem = Csv.Document(Web.Contents("https://zumub.com/internal/lab_production_plans_products.csv"),[Delimiter=",", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Cabeçalhos Promovidos" = Table.PromoteHeaders(Origem, [PromoteAllScalars=true]),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Cabeçalhos Promovidos",{{"id", Int64.Type}, {"lab_production_plans_id", Int64.Type}, {"products_id", Int64.Type}, {"attributes_id", Int64.Type}, {"calibration_id", Int64.Type}, {"quantity", Int64.Type}, {"comments", type text},{"quantity_produced", Int64.Type},{"quantity_produced_updated_admin", Int64.Type}, {"quantity_produced_updated_date", type datetime},{"created_at", type datetime},{"products_model", type text}, {"products_name", type text}, {"attribute", type text}}),
#"Colunas com Nome Mudado" = Table.RenameColumns(#"Tipo Alterado",{{"quantity", "quantity planned"}}),
#"Valor Substituído" = Table.ReplaceValue(#"Colunas com Nome Mudado","grams","g",Replacer.ReplaceText,{"products_name"}),
#"Tipo Alterado1" = Table.TransformColumnTypes(#"Valor Substituído",{{"quantity_produced", type number}})
in
#"Tipo Alterado1"
>

[BA_Pete]

Thanks for sending this. Is this column full of errors when the file is first imported?

I'm wondering whether changing the column type using Locale might help.

Select any step BEFORE this column gets errors then select the column, right-click on the column header and go to Change Type > Using Locale... :

In the dialog, set the Data type to Decimal Number, then set the Locale to UK (the UK use '.' as a decimal separator so, if your country uses ',' this should convert correctly.):

Pete

[FilipeBarroso]

Hello,

The file gives error once we open in Power Query and refresh the visualization. It gives error in whole number and decimal type. 

Also tried suggestion of using Locale and changing to UK but also didn´t work.

😞