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 all,
I know this topic has already been treated. However I opened every similar topics and none of the solutions given are working for me.
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)
Problem is : I want this two columns to give me decimals as we can see them in the Excel file.
Here is what I tried to fix it :
- 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)
- 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)
- 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)
- For information there are Decimals in the first 200 records so the problem doesn't come from this side.
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.
Sorry for my bad english, I'm working on it ^^
Thanks for consideration,
Cado
Solved! Go to Solution.
@Cado_one , In Edit Query/ Data Transformation , Right click on table And there option for Advance Editor
You will script like
let
Source = Excel.Workbook(File.Contents(".....ig.xlsx"), null, true),
Sales_Sheet = Source{[Item="Sales",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sales_Sheet, [PromoteAllScalars=true]),
#"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}}),
Something like
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"COGS", "COGS Unit"}, {"Deilvery Date", "Delivery Date"}})
in
#"Renamed Columns"
Here Go ahead a change data type to number or Decimal or double like {"COGS", type number}
@Cado_one , In Edit Query/ Data Transformation , Right click on table And there option for Advance Editor
You will script like
let
Source = Excel.Workbook(File.Contents(".....ig.xlsx"), null, true),
Sales_Sheet = Source{[Item="Sales",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sales_Sheet, [PromoteAllScalars=true]),
#"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}}),
Something like
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"COGS", "COGS Unit"}, {"Deilvery Date", "Delivery Date"}})
in
#"Renamed Columns"
Here Go ahead a change data type to number or Decimal or double like {"COGS", type number}
Thank you very much @amitchandak you solved my issue !
I hope this will help other people having the same problem 🙂
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |