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 65.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). 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

[Greg_Deckler]

I would go with the 65,000 rows, that's nothing. A few millions or even tens of millions of rows is child's play for Power BI.

[lasse0hlsen]

Hey @Greg_Deckler! Many thanks for your prompt 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

[lasse0hlsen]

Hey everyone! 

Yesterday I found a partial solution to my above-mentioned problem. I realised that the relationships between the main query "Projects" and the other queries ("Countries" and "Result Areas", and more to come) are many-to-one relationships instead of m2m relationships as I initially thought. Here is a screenshot of the cardinalities in my current data model:
 

Since one cannot manually select the cross filter direction in PowerPivot in a simple drop-down menu (unlike in Power BI), I had to use a DAX formula that would allow me to cross filter the columns "Total GCFFunding", "TotalCoFinancing" and "Total Value" (which are all three part of the "Projects" query) by the "CountryName" that is a column in the "Countries" query. The DAX measure looks as follows: TotalGCFFundingCF:=CALCULATE([Summe von TotalGCFFunding]; CROSSFILTER(Projects[ProjectsID]; Countries[ProjectsID];Both)) This way, I could avoid that Excel sums up the values for the individual projects several times: 

 

Now I have only one outstanding question: Can you think of a DAX formula that would allow me to filter the projects (from the "Projects" query) by Areas (a column from the "Result Areas" query that contains text information such as 'Ecosystem & ecosystem services', 'Energy access & power generation', etc.)? I would need something like the CALCULATE / CROSSFITLER function just for text instead of numeric values...

Thanks for bearing with my long question! I look forward to your suggestions! 🙂 

Cheers, 

Lasse