cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: 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:

  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
Highlighted
Super User VI
Super User VI

Re: 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:

  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

Highlighted
Helper I
Helper I

Re: information standardization

@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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors