Forum Discussion

Torbennb's avatar
Torbennb
Frequent Visitor
1 year ago
Solved

Connect to data in object

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: (ex...
Need Help
  • saud968's avatar
    saud968
    1 year ago

    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!

saud968's avatar
saud968
Memorable Member
1 year ago

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!