Difficulties in creating many-to-many relationships in PowerPivot

[lasse0hlsen]

Hey guys,

I am currently trying to build a database in Excel that contains all Green Climate Fund (GCF) projects. Recently the GCF published an API that allows internet users to access their project data through the following URL: https://api.gcfund.org/v1/projects. I used the "Get Data from the Web with Power Query" function in Excel (as part of Office 365) in order to import the .JSON file containing the GCF project data. After converting the list of records into a table, expanding the different columns and inspecting the data in the PQ Editor, it it became clear that the .JSON file contains several nested values (more precisely, the columns "Countries", "Entities", "Disbursements", "Funding" and "ResultAreas" each contain many lists of records the lead to further lists of records - according to my understanding it's a "table in a table in a table" ). Instead of expanding all lists of records in one table as part of a single query (where I would have ended up with over 50.000 rows, as the PQ Editors copies down the table entries on the left the more columns get expanded on the right), I decided to create an individual query for each of the above-mentioned columns. I loaded these queries as connections only and added them to the data model (for further processing in PowerPivot). Afterwards, I merged these queries with the "Project" query and left the "ProjectID" column as the matching column, so that the left outer join function could do its magic. The query dependencies at that stage looked as follows:

In the attached Excel file you can see that I have added all six queries to the data model in PowerPivot. For further analysis of the GCF project data I have added the following filters in a PivotTable: Theme, Sector, Area, Access, Region, CountryName, Size, BoardMeeting and ApprovalDate. It is important to note that these filters are each connected with a different query and until this point I did not manage to create the relationships between the different queries in the diagram view in PowerPivot. Thus, I was not able to filter the GCF projects by for example ResultAreas. I have tried to create many-to-many relationships between the different queries with the help of this Youtube tutorial, however, I did not succeed. Could anyone of you please have a look at my file and let me know how I can possibly solve the previously described issue?

I would totally appreciate your help / any helpful suggestions! Many thanks in advance!

Cheers,
lasse0hlsen

 

[lasse0hlsen]

Little correction to my previous post: I did not merge the queries in the PQ Editor, as opposed to what I wrote above! I mistakenly mentioned this, but it's not the case. In the screenshot of the query dependencies you can see that the individual queries have not been merged. I think this is an important thing the mention...

 

[JustynaMK]

Hi lasse0hlsen,

This might not be the most pretty way to do that but... here it goes... I'd suggest trying to stick to one Query. For example, copy projects query and go back a few steps:

Click on an "arrow" symbol next to Countries column and expand all columns (you can also choose to use "original column name as prefix" - this will display Countries.CountryID instead of CountryID). You will notice that Power Query is automatically recognizing a relationship between your main "ProjectsID" column (projects) and "ProjectsID" in Countries.

Continue the process for all remaining queries (Entities, Disbursements...). Once loaded, your merged query should have almost 65k rows. You can now use this "main" query as your one & only source.

 

[lasse0hlsen]

Hey @JustynaMK! Many thanks for your response! I have already tried to expand all lists of records in one table - here is the result. I loaded the 65.000 row table to the data model in order to analyse the dataset in a PivotTable. Then I set a few filters and tried to filter the dataset so Excel would show me all "Mitigation" projects in "Mongolia":

Unfortunately I had to find out that Excel does not show the values for "TotalGCFFunding", "TotalCoFinancing" and "TotalValue" individually for each project, but adds them up, because the Power Query Editor copied the individual projects from the table downwards when it was expanded - so it is logical that the PivotTable forms the sum of these values:

Unfortunately I don't know how to display the unique values for each individual project. Can you give me any advice?

Cheers,
lasse0hlsen