Connect to data in object

[Torbennb]

Our developers suggest to deliver data endpoint for reporting in this format:

(Purpose is to get the relation between groups and members of groups)

Would that be possible to use in Power Bi: (expand in Power Quiry)

[
  {
    "GroupId": "1",
    "MemberIds": [
      "1",
      "2",
      "3",
      ...
    ]
  },
  ...
]

I have the relation between the memberid and the member in another table.

[Torbennb]

Thank you very much for confirming.

[saud968 · Accepted solution]

you can use this data format in Power BI and expand it in Power Query. Here's how you can do it: Load the JSON Data: In Power BI, go to Home > Get Data > JSON. Load your JSON file. Expand the Data in Power Query: Once the data is loaded, it will appear in the Power Query Editor. You will see a column named MemberIds which contains lists of member IDs. Click on the Expand icon (two arrows) next to the MemberIds column to expand the lists into individual rows. Merge with Member Table: Load your member table into Power BI. In Power Query Editor, go to Home > Merge Queries. Select the GroupId and MemberId columns from both tables to create the relationship. Finalize and Load: After merging, you can perform any additional transformations if needed. Click Close & Apply to load the data into Power BI. This will allow you to analyze the relationship between groups and their members effectively.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!