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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors