Forum Discussion
data converted automatically to decimal numbers
Hi MAIK2705 ,
This can be resolved by following a couple of best-practice guidelines:
1) Structure your data correctly for reporting (normalisation):
After your navigation step, select your [FundId] column and go to the Transform tab > Unpivot Column (dropdown) > Unpivot Other Columns.
You will now have a normalised fact table structure with an [Attribute] and [Value] column.
You can change the names of these if you want by amending the code that has been created in the formula bar:
At this point, you can just change the Data Type of the new [Attribute] and [Value] columns. This will always work as you're no longer hard-coding specific column names into the transformation code.
2) Type your data correctly (standard data types):
Do not use the percentage data type in Power Query. It's just a mask over a Decimal type anyway. Change your new [Value] column to Decimal type and adjust the format under MEasure Tools in the Report View:
Pete
The root cause is that your column names change, so Excel doesn't keep the formatting because there are new column names. Before loading to Excel, demote the headers, then change your column types. Yes, your columns will be named Column1, Column2, etc, but your data will remain typed, and you can always hide the first row in Excel.
--Nate
Hi watkinnc and thanks for the help,
However this is not the cause because is not only Excel, i tried uploading the data in CSV, with Gateway and MySQL and i have the same prob always. After formatting in power query its converted.
Thanks
