Using OData many to many associations in Power BI

3
Hi,   I've managed to create charts in Power BI using OData from a Mendix app, but only when representing associations as associated object id's (which makes sense to me). However, if I want to use many-to-many associations, it's only possible to expose them if the associations are represented as links. I haven't figured out how to relate the objects (like this) in Power BI. Has anyone been able to achieve this?   Many thanks   Gonçalo
asked
3 answers
1

I want to expose my accounts and companies to PowerBI with OData, but OData has its limitations.

OData services have a option to publish many to many associtions as links since 7.21. This link looks like this https://app.mendixcloud.com/odata/app/parties(22799473113563137)/Accounts. 

This is not a table with a account id and party id. We can use these links in Azure if we write custom logic in Azure, but we don't want that, so we are going to stick to the solution in 8618 going to create a account – party entity.

@Mendix I dont like your solution for idea 564 and would like to expose a many to many association as an association table.

https://forum.mendix.com/link/questions/8618

https://forum.mendix.com/link/questions/88818

https://forum.mendix.com/link/questions/95322

Best regards

 

 

answered
0

Leaving the answer here for anyone searching! If you are exposing a many-to-many association as an OData link in Mendix, there is still hope to create a relationship in Power BI!!

In Power BI, go to the ‘Transform Data’ tab and select the table which matters less to you [technically you can select either, but I didn’t want to mess with my main table]

Duplicate this table. [you can also choose to clean up the columns – delete everything except the primary key and the nested table]

If you’ve exposed the association with a link, you should have these nested tables in the last column of your tables. Click the ‘expand table’ button on the top right of the column and only select the ID or Number that is in your other table [ex: I selected Request Number because my other table is full of requests and that’s my primary key]

Now you should have a giant table with all associations between both tables – congrats, this is now a reference table! Rename your reference table, close/apply, and go to your model view.

From your model view, drag your primary key to the same key on the reference table. CONGRATS, YOU’RE DONE!

answered
-2

Hi Goncalo,

This issue was fixed in 7.21.0:

https://docs.mendix.com/releasenotes/studio-pro/7.21

This should allow you to expose many-to-many associations.


Regards ​​​​​​​

answered