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
Solution Sage
Solution Sage

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

View solution in original post

ebeery
Solution Sage
Solution Sage

@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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors