Search code examples
powerbipowerbi-desktopm

Build one to many relationship in PowerBI, when foreign keys are inside JSON array?

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:

users

[
    {
    "email": "user1@foo-bar.com",
    "user_id": "user1-id",
    "factory": ['factoryId1']
  },
  {
    "email": "user2@foo-bar.com",
    "user_id": "user2-id",
    "factory": ['factoryId2', 'factoryId1']
  }
]

factories

[
  {
    "factoryId": 'factoryId1',
    "factoryName": "London"
  },
  {
    "factoryId": 'factoryId2',
    "factoryName": "Moscow"
  }
]

Now the relationship I would like to build is:

+----------+------------+
| user_id  | factory_Id |
+----------+------------+
| user1-id | factoryId1 |
| user2-id | factoryId1 |
| user2-id | facotryId2 |
+----------+------------+

I have tried using the "Add as new Query" option:

enter image description here

After converting to a table and expanding the rows, I almost get the desired result:

+------------+
| factory_Id |
+------------+
| factoryId1 |
| factoryId1 |
| facotryId2 |
+------------+

The only thing missing here is the userIds. How do I add those Ids? (or maybe there is a simpler method of doing this?)

Solution

  • Use "Expand to New Rows" (click on the double arrow icon)

    enter image description here enter image description here

    The M query will look like this:

    let
    Source = Json.Document(File.Contents("C:\Users\gluisotto\Desktop\json.json")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"email", "user_id", "factory"}, {"Column1.email", "Column1.user_id", "Column1.factory"}),
    #"Expanded Column1.factory1" = Table.ExpandListColumn(#"Expanded Column1", "Column1.factory")
in
    #"Expanded Column1.factory1"