Hi everyone...
I am stumped with this one. I am trying to combine data between 2 tables and can't figure out how to do it, with PowerPivot, relationships or pivot tables...
Essentially, the data is the following: I have SharePoint site collections and sub sites for which I pulled membership data. The sub sites are inheriting the membership data from the site collections, however in the Excel extract the sub sites don't have the data yet, so I want to create/add that data.
Essentially, right now I have 2 sets of data.
1. A list of the site collections and the sub sites inheriting permissions from them (Source 1)
2. Another list with the site collections and the membership data (e-mails) for each site collection (Source 2)
How do I consolidate them? Without doing this row by row of course. The sample data I showed you is just for benchmarking purposes. I manually drew up what I'd like my solution to look like (Desired outcome). In reality I have roughly 8000 rows of data and many more columns. I'm looking for a reusable methodology or "hack"...I'd prefer an Excel solution, but I'm also open to using PowerBI only if need be. I'm pretty strong with using pivot tables but not yet with Power BI...
Thanks in advance!
Data conundrum.PNG