topic Decimal numbers from Excel automatically getting rounded and converted to integers in PowerBI in Desktop
https://community.powerbi.com/t5/Desktop/Decimal-numbers-from-Excel-automatically-getting-rounded-and/m-p/1087536#M503675
<P>Hi all,</P><P> </P><P>I know this topic has already been treated. However I opened every similar topics and none of the solutions given are working for me.</P><P> </P><P>Let's explain, I got 8 columns in an excel file which contains either integers, decimals numbers or void. The data type in Excel is standard. When importing in Power BI, 6 of these columns are doing well but 2 of them are getting rounded and considered as integers. (for example 1,75 becomes 2 and 0,25 becomes 0)</P><P> </P><P>Problem is : I want this two columns to give me decimals as we can see them in the Excel file.</P><P> </P><P>Here is what I tried to fix it :</P><P>- Change the two column's Data type for Text in PBI and in Power Query editor (the 6 working columns have a text data type) </P><P>- Change the two column's Data type for Decimal in PBI and in Power Query editor with 2 decimal digits, it gives me only zeros after the coma (for example 1,75 in excel becomes 2,00 in PBI)</P><P>- Change the two column's Data type for Text in Excel (in other topics this solution seems to work for some people but it isn't for me and changing data type on excel is not okay for me because the source file will be often changed and I can't have to change data type each time)</P><P>- For information there are Decimals in the first 200 records so the problem doesn't come from this side.</P><P> </P><P>None of the above solutions did the job and I have no idea of what I could try more. That's why I post the topic here with a strong hope that someone will be able to help me.</P><P> </P><P>Sorry for my bad english, I'm working on it ^^</P><P>Thanks for consideration,</P><P>Cado</P>Wed, 13 May 2020 09:24:33 GMTCado_one2020-05-13T09:24:33ZDecimal numbers from Excel automatically getting rounded and converted to integers in PowerBI
https://community.powerbi.com/t5/Desktop/Decimal-numbers-from-Excel-automatically-getting-rounded-and/m-p/1087536#M503675
<P>Hi all,</P><P> </P><P>I know this topic has already been treated. However I opened every similar topics and none of the solutions given are working for me.</P><P> </P><P>Let's explain, I got 8 columns in an excel file which contains either integers, decimals numbers or void. The data type in Excel is standard. When importing in Power BI, 6 of these columns are doing well but 2 of them are getting rounded and considered as integers. (for example 1,75 becomes 2 and 0,25 becomes 0)</P><P> </P><P>Problem is : I want this two columns to give me decimals as we can see them in the Excel file.</P><P> </P><P>Here is what I tried to fix it :</P><P>- Change the two column's Data type for Text in PBI and in Power Query editor (the 6 working columns have a text data type) </P><P>- Change the two column's Data type for Decimal in PBI and in Power Query editor with 2 decimal digits, it gives me only zeros after the coma (for example 1,75 in excel becomes 2,00 in PBI)</P><P>- Change the two column's Data type for Text in Excel (in other topics this solution seems to work for some people but it isn't for me and changing data type on excel is not okay for me because the source file will be often changed and I can't have to change data type each time)</P><P>- For information there are Decimals in the first 200 records so the problem doesn't come from this side.</P><P> </P><P>None of the above solutions did the job and I have no idea of what I could try more. That's why I post the topic here with a strong hope that someone will be able to help me.</P><P> </P><P>Sorry for my bad english, I'm working on it ^^</P><P>Thanks for consideration,</P><P>Cado</P>Wed, 13 May 2020 09:24:33 GMThttps://community.powerbi.com/t5/Desktop/Decimal-numbers-from-Excel-automatically-getting-rounded-and/m-p/1087536#M503675Cado_one2020-05-13T09:24:33ZRe: Decimal numbers from Excel automatically getting rounded and converted to integers in PowerBI
https://community.powerbi.com/t5/Desktop/Decimal-numbers-from-Excel-automatically-getting-rounded-and/m-p/1087582#M503693
<P><LI-USER uid="230296"></LI-USER> , In Edit Query/ Data Transformation , Right click on table And there option for Advance Editor</P>
<P>You will script like</P>
<P>let<BR />Source = Excel.Workbook(File.Contents(".....ig.xlsx"), null, true),<BR />Sales_Sheet = Source{[Item="Sales",Kind="Sheet"]}[Data],<BR />#"Promoted Headers" = Table.PromoteHeaders(Sales_Sheet, [PromoteAllScalars=true]),<BR />#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Order No", Int64.Type}, {"Item ID", Int64.Type}, {"Sales Date", type date}, {"Deilvery Date", type date}, {"Customer Id", Int64.Type}, {"City Id", Int64.Type}, {"Qty", Int64.Type}, {"Price", Int64.Type}, {"COGS", Int64.Type}, {"Discount Percent", Int64.Type}}),</P>
<P> </P>
<P>Something like <BR />#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"COGS", "COGS Unit"}, {"Deilvery Date", "Delivery Date"}})<BR />in<BR />#"Renamed Columns"</P>
<P> </P>
<P>Here Go ahead a change data type to number or Decimal or double like {"COGS", type number}</P>Wed, 13 May 2020 09:47:00 GMThttps://community.powerbi.com/t5/Desktop/Decimal-numbers-from-Excel-automatically-getting-rounded-and/m-p/1087582#M503693amitchandak2020-05-13T09:47:00ZRe: Decimal numbers from Excel automatically getting rounded and converted to integers in PowerBI
https://community.powerbi.com/t5/Desktop/Decimal-numbers-from-Excel-automatically-getting-rounded-and/m-p/1087765#M503781
<P>Thank you very much <LI-USER uid="148838"></LI-USER> you solved my issue !</P><P> </P><P>I hope this will help other people having the same problem <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span></P>Wed, 13 May 2020 11:42:52 GMThttps://community.powerbi.com/t5/Desktop/Decimal-numbers-from-Excel-automatically-getting-rounded-and/m-p/1087765#M503781Cado_one2020-05-13T11:42:52Z