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
Hi BA_Pete and thanks for the answer,
1- I know maybe is not the most apropiate structure, and maybe i should normalise. Nevertheless, this table was just a sample, usually i have less columns FundID, Total Ret YTD, Total Ret 1Y, Total Ret 3Y and Total Ret 5Y, management fees etc...
That's why I thought normalising this type of data wasnt a priority because in this performance table i dont use dates and other reasson is that if i unpivot around 40k differents rows this could affect the performance of the report. Im wrong or i should normalize it ?
2- I knew this way and following the approach of unpivot tables make sense, but imagine that one day i have lots of columns with % types and i should do it one by one . I think is not optimal.
However, i think what you said is the reasson "Do not use the percentage data type in Power Query. It's just a mask over a Decimal type anyway. " but anyway I think this is something which should be corrected.
I think i will go for you second approach and hope not having lots of columns to change one day .
Thank you vey mach for the help.
Hi MAIK2705 ,
My options 1) and 2) weren't really either/or, I recommend you do them BOTH.
1) Yes, unpivoting can be resource-intensive (as the whole table has to be loaded into memory) and may affect refresh speed but, as you get into using Power BI more, you'll see that normalised data makes everything else much easier once done. Power BI can compress data orders of magnitude more efficiently once it's normalised (so the PBIX file is much smaller and faster to publish etc.), relationships can be made between normalised tables far more intuitively, and FAR fewer measures have to be written.
2) This is another reason why you normalise the structure first where possible: so you only need to change the Type of your [Value] column. However, if you do have columns that logically need to stay as their own columns, then you can multi-select them (Ctrl+click or Click Shift+click), right-click on one of the selected columns title, go to Change Types and select a type to change ALL the currently selected columns. This makes bulk type changes very fast, just be mindful that the column names will now be hard-coded into the transformation.
Pete