Forum Discussion
Transform List into rows best approach
Hi mcantos ,
Thanks for reaching out to the Microsoft fabric community forum.
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.
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"