cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alalan
Frequent Visitor

Help to define metadata

Hi Folks!

I need your brain to know how do I create a metadata for my database which will be captured from the CRM and I will use for reporting later.

As you can see in the attached picture, I have project name and what services we provided them. Later, while reporting I would like to know how many projects took "abc" services from us. I don't want to duplicate rows for same project name and different services as well as how do I capture this data from the CRM.

 

Capture.JPG

2 ACCEPTED SOLUTIONS
ebeery
Super User
Super User

@alalan can you explain what you mean by "I don't want to duplicate rows for same project name and different services"?

It seems to me that in order to make any meaningful use of this data it would need to be in the form:

Project Name - externalServicesSector
Test1Interior DesignHospitality
Test1ConstructionHospitality
Test1DocumentationHospitality
Test1Construction ServicesHospitality
Test1Town PlanningHospitality
Test2FeasibilityMulti Residential
Test2Town PlanningMulti Residential
Test2Interior DesignMulti Residential
Test2ConstructionMulti Residential
Test2DocumentationMulti Residential
Test2Construction ServicesMulti Residential


If you wanted to further normalize and reduce the redundancy of the "Sector" info, you could have two tables like below, related through a one-to-many relationship.

Project Name - externalServices
Test1Interior Design
Test1Construction
Test1Documentation
Test1Construction Services
Test1Town Planning
Test2Feasibility
Test2Town Planning
Test2Interior Design
Test2Construction
Test2Documentation
Test2Construction Services

 

Project Name - externalSector
Test1Hospitality
Test2Multi Residential

 

View solution in original post

Ashish_Mathur
Super User
Super User

Hi,

With the current data layout, there is nothing that you can do at all.  To get any meaningful analysis, you must ensure that each service appears in its own cell (which also means that in the Project Name and Sector columns, the entries will repeat.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

With the current data layout, there is nothing that you can do at all.  To get any meaningful analysis, you must ensure that each service appears in its own cell (which also means that in the Project Name and Sector columns, the entries will repeat.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ebeery
Super User
Super User

@alalan can you explain what you mean by "I don't want to duplicate rows for same project name and different services"?

It seems to me that in order to make any meaningful use of this data it would need to be in the form:

Project Name - externalServicesSector
Test1Interior DesignHospitality
Test1ConstructionHospitality
Test1DocumentationHospitality
Test1Construction ServicesHospitality
Test1Town PlanningHospitality
Test2FeasibilityMulti Residential
Test2Town PlanningMulti Residential
Test2Interior DesignMulti Residential
Test2ConstructionMulti Residential
Test2DocumentationMulti Residential
Test2Construction ServicesMulti Residential


If you wanted to further normalize and reduce the redundancy of the "Sector" info, you could have two tables like below, related through a one-to-many relationship.

Project Name - externalServices
Test1Interior Design
Test1Construction
Test1Documentation
Test1Construction Services
Test1Town Planning
Test2Feasibility
Test2Town Planning
Test2Interior Design
Test2Construction
Test2Documentation
Test2Construction Services

 

Project Name - externalSector
Test1Hospitality
Test2Multi Residential

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!