Forum Discussion
information standardization
Hi jcamilo1985 ,
It is hard to say specifically as I don't know what your end goal is, but as a general rule values should be unpivoted and leave text fields alone. That is not a hard and fast rule, just a general rule. What I did:- Converted your YYYYMM to a true date field for the end of the month. This will make relating to a true date table a snap.
- Set the data type for all other fields as text or integer. You can set the latter to number if you need decimals, but none of your data appeared to have that.
- Kept the date and text fields, and unpivoted all other fields.
See the file attached here which should get you started. See the "normalized" tab for the work I did. You can see the transformations in Power Query itself. There is an article below on normalizing, as well as the importance of a Star Schema in Power BI, of which getting your table normalized is key, so you are definitely on the right track.
What is normalizing data?
See the Normalizing an example table section of this paperMicrosoft Guidance on Importance of Star Schema
If you have more specific needs in visuals or calculations, please post back with some expected results and we can assist in tweaking how the data is normalized for your specific model.
Hi jcamilo1985 ,
It is hard to say specifically as I don't know what your end goal is, but as a general rule values should be unpivoted and leave text fields alone. That is not a hard and fast rule, just a general rule. What I did:
- Converted your YYYYMM to a true date field for the end of the month. This will make relating to a true date table a snap.
- Set the data type for all other fields as text or integer. You can set the latter to number if you need decimals, but none of your data appeared to have that.
- Kept the date and text fields, and unpivoted all other fields.
See the file attached here which should get you started. See the "normalized" tab for the work I did. You can see the transformations in Power Query itself. There is an article below on normalizing, as well as the importance of a Star Schema in Power BI, of which getting your table normalized is key, so you are definitely on the right track.
What is normalizing data?
See the Normalizing an example table section of this paper
Microsoft Guidance on Importance of Star Schema
If you have more specific needs in visuals or calculations, please post back with some expected results and we can assist in tweaking how the data is normalized for your specific model.
edhans Thank you very much for the quick response, really thank you very much, that is why I love this community.
Annex link full archive where you currently find the outline of the report as well as the necessary files.
I know that the data model is horrible and it can be improved, but I do not find it easy to do it, for example you will find that the book "executive sales" has both quantities and sales amounts and I was wondering if in the normalization it is correct to leave it in a single attribute , I appreciate you can guide me.