Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jcamilo1985
Helper III
Helper III

information standardization

good morning experts
very recently I have been facing a professional challenge with production data. I am nervous because things are not going as I would like, so all the help you can give me, I will be grateful.
I have the following table (sample)  onedrive table  and I need to start normalizing it to make it work fine in the data model
How should it look?

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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:

  1. 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.
  2. 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.
  3. 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
edhans
Super User
Super User

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:

  1. 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.
  2. 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.
  3. 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors