Data consolidation conundrum...

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

Hi Andrew.

The image isn't showing for me. Would you mind re-uploading?

Thanks,

Ben

Sorry Andrew, have just managed to get the image to show and have solved your issue using Power Query (Get and Transform).

See the attached example.

Head to the Data tab, click into the first table, and then click 'Get data from Table'. Close and load to a table.

Click into the second table, close and load while creating connection only.

Go back into the first query, merge the query with the second table and bring through the email address.

This is by the far the simplest approach.

Hi Ben, thank you so much for the quick and effective solution!
I am new to using Power Query so forgive me, but I got stuck replicating your solution at this step:
"Click into the second table, close and load while creating connection only."
Would you mind expanding on that step please?

Andrew

Hi Andrew,

Hopefully these screenshots will help. Click Close and Load To, then another window will open. Click 'Connection only'.

BenAttachment 666540

Attachment 666541

Thanks,

Ben

Thanks Ben that solution works great!
Much appreciated!