title: Build one to many relationship in PowerBI, when foreign keys are inside JSON array?
body: <p>I am very new with Power BI, and my first task is to connect to a JSON datasource, I have an issue with arrays containing keys. Some context, let's say we have users and factories:</p>
  
  <p><strong>users</strong></p>
  
  <pre class="lang-js prettyprint-override"><code>[
      {
      "email": "user1@foo-bar.com",
      "user_id": "user1-id",
      "factory": ['factoryId1']
    },
    {
      "email": "user2@foo-bar.com",
      "user_id": "user2-id",
      "factory": ['factoryId2', 'factoryId1']
    }
  ]
  </code></pre>
  
  <p><strong>factories</strong></p>
  
  <pre><code>[
    {
      "factoryId": 'factoryId1',
      "factoryName": "London"
    },
    {
      "factoryId": 'factoryId2',
      "factoryName": "Moscow"
    }
  ]
  </code></pre>
  
  <p>Now the relationship I would like to build is:</p>
  
  <pre><code>+----------+------------+
  | user_id  | factory_Id |
  +----------+------------+
  | user1-id | factoryId1 |
  | user2-id | factoryId1 |
  | user2-id | facotryId2 |
  +----------+------------+
  </code></pre>
  
  <p>I have tried using the "Add as new Query" option:</p>
  
  <p><a href="https://i.stack.imgur.com/qW7Fi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qW7Fi.png" alt="enter image description here"></a></p>
  
  <p>After converting to a table and expanding the rows, I <strong>almost</strong> get the desired result:</p>
  
  <pre><code>+------------+
  | factory_Id |
  +------------+
  | factoryId1 |
  | factoryId1 |
  | facotryId2 |
  +------------+
  </code></pre>
  
  <p>The only thing missing here is the userIds. How do I add those Ids? (or maybe there is a simpler method of doing this?)</p>