Transform List into rows best approach

[mcantos]

Hello community,

I need your help again, please.

I have two tables I retrieve from an integration in the  form of JSON:

Opportunities and internalContacts, where an opportunity can have 0 or multiple contacts, and a contact can be linked to 0 or multiple opportunities.

The data from the Opportunities table is fine, but I’m getting a problem when trying to set the internalContacts and related it to the Opportunities table.   

Here is the JSON from internalContacts

[
{
"Contact ID": 300
"First name": "Dana",
"Last name": "Heji",
"Opportunity role": "Transaction Lead; Manager",
"Opportunity ID": [
1000, 1001
]
},
{
"Contact ID": 301
"First name": "Maria",
"Last name": "Martinez",
"Opportunity role": "Business Development Manager; Admin",
"Opportunity ID": [
1003, 1005
]
},
{
"Contact ID": 302
"First name": "Angel",
"Last name": "Short",
"Opportunity role": "; Developer",
"Opportunity ID": [
1005, 1000
]
},
{
"Contact ID": 302
"First name": "Angel",
"Last name": "Short",
"Opportunity role": null,
"Opportunity ID": []
},
{
"Contact ID": 302
"First name": "Angel",
"Last name": "Short",
"Opportunity role": null,
"Opportunity ID": []
},
{
"Contact ID": 302
"First name": "Angel",
"Last name": "Short",
"Opportunity role": null,
"Opportunity ID": []
}
]

Here is my Opportunities table

For internalContacts I transformed the Opportunity ID List into text separated by  comma

 

And then, I split the columns Opportunity role and Opportunity ID by delimiter into rows, so I could get  the corresponding Opportunity ID with its role

The problem is that when I try to retrieve information from both tables, I’m only getting the opportunities that have internal contacts linked, and I need all of them, including the ones without contacts.

So, it seems like I need to create a new table, a flattened table, but my idea is to keep the data model as simple and maintainable as possible. Do you have any ideas on a new approach that I can apply? 

I tried to create measures, but it didn't work either. 

Would it make any difference if the JSON change into something like this?

  {

    "Contact ID": 300

               "First name": "Dana",

    "Last name": "Heji",

               "Opportunity and role": [

      {

        "Opportunity ID": 13112,

        "Opportunity role": "Transaction Lead"

      }

    ]

  },

Thank you in advance

[v-lgarikapat · Accepted solution]

Hi @mcantos ,

Thanks for reaching out to the Microsoft fabric community forum.

@lbendlin 

Thanks for your prompt response

For reference, I have included the learning document and previously solved threads, which may help you resolve the issue.

Solved: Re: Normalising nested json data - Microsoft Fabric Community

Solved: Transform Data, List into individual rows - Microsoft Fabric Community

Json.Document - PowerQuery M | Microsoft Learn

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

If this post helped resolve your issue, please consider the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana.

[lbendlin]

The sample JSON is malformed, missing commas after the Contact ID.  

let
    Source = "[
{
""Contact ID"": 300,
""First name"": ""Dana"",
""Last name"": ""Heji"",
""Opportunity role"": ""Transaction Lead; Manager"",
""Opportunity ID"": [
1000, 1001
]
},
{
""Contact ID"": 301,
""First name"": ""Maria"",
""Last name"": ""Martinez"",
""Opportunity role"": ""Business Development Manager; Admin"",
""Opportunity ID"": [
1003, 1005
]
},
{
""Contact ID"": 302,
""First name"": ""Angel"",
""Last name"": ""Short"",
""Opportunity role"": ""; Developer"",
""Opportunity ID"": [
1005, 1000
]
},
{
""Contact ID"": 302,
""First name"": ""Angel"",
""Last name"": ""Short"",
""Opportunity role"": null,
""Opportunity ID"": []
},
{
""Contact ID"": 302,
""First name"": ""Angel"",
""Last name"": ""Short"",
""Opportunity role"": null,
""Opportunity ID"": []
},
{
""Contact ID"": 302,
""First name"": ""Angel"",
""Last name"": ""Short"",
""Opportunity role"": null,
""Opportunity ID"": []
}
]",
J = Json.Document(Source),
    #"Converted to Table" = Table.FromList(J, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Contact ID", "First name", "Last name", "Opportunity role", "Opportunity ID"}, {"Contact ID", "First name", "Last name", "Opportunity role", "Opportunity ID"}),
    #"Expanded Opportunity ID" = Table.ExpandListColumn(#"Expanded Column1", "Opportunity ID")
in
    #"Expanded Opportunity ID"