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.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!