cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Difficulties in creating many-to-many relationships in PowerPivot

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:

 

1582206618681.png

 

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

3 REPLIES 3
Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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": 

1.JPG

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: 
 
2.JPG
Unfortunately I don't know how to display the unique values for each individual project. Can you give me any advice?
 
Cheers, 
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:
 1234.JPG

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: 

 12345.JPG

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors