PowerBI: Many-to-many relationship with collection specified on the one side of the relationship

Question

Scenario:

• There is a many-to-many relationship between orders and widgets. I.e. an order contains several widgets, a widget can be contained in several orders
• In the source data (two JSON documents orders.json and widgets.json) the relationship is represented by a in_orders collection on the widget side.

Example:

// orders.json
[
  { order_id: 1, ...},
  { order_id: 2, ...},
  { order_id: 3, ...},
]

// widgets.json
[
  { widget_id: 1, in_orders: [2, 3], ...},
  { widget_id: 2, in_orders: [1], ...},
  { widget_id: 3, in_orders: [], ...}
]

Question(s):

• What is the recommended way to represent this as a meaningful many-to-many relationship in Power BI?
• The end result would let me filter a) all widgets on specific order and b) all orders that contain a specific widget

Answer 1

In Excel, the best practice way is to have a table of widgets, a table of orders, and a table of widget-order linkages.

If it were me, I would have six queries.

orders_json would simply be the orders.json file imported and parsed. widgets_json would simply be the widgets.json file imported and parsed.

I do this just because I like to have the "raw" data as their own queries. Then...

orders would be a table of orders, referencing orders_json and doing whatever makes the most sense to clean up the date.

widgets_raw would be a table of widgets where the "in_orders" column contains numeric lists.

widgets would start with widgets_raw, remove the "in_orders" column, and then do whatever makes the most sense up clean up the data.

orders_widgets would start with widgets_raw, select only the "widget_id" and "in_orders" columns, expand the "in_orders" column, and rename the "in_orders" column to "orders_id".

Exporting these last three queries to the data model lets you add a one-to-many relationship between orders & orders_widgets, and widgets & orders_widgets.

In PowerBI, there may be a difference I'm unaware of re "data model" and setting up one-to-many relationships, but this should get you most of the way there. Try it and let me know?