I am currently working with JSON data in Power BI and I need assistance in extracting and transforming the data into separate tables based on the different key-value pairs in each JSON. I have multiple JSONs with varying structures, and I want to ensure that all the JSONs are properly transformed into individual tables within Power BI.

Here are some sample JSON datasets that I am working with:
Employee Table:
JSON

[
{
“EmpID”: 1001,
“EmpName”: “John Doe”,
“Salary”: 75000,
“DateOfBirth”: “1980-01-01”,
“CreatedDateTime”: “2023-01-01T00:00:00Z”
},
{
“EmpID”: 1002,
“EmpName”: “Jane Smith”,
“Salary”: 82000,
“DateOfBirth”: “1985-07-15”,
“CreatedDateTime”: “2023-02-15T00:00:00Z”
},
{
“EmpID”: 1003,
“EmpName”: “Michael Lee”,
“Salary”: 68000,
“DateOfBirth”: “1990-04-22”,
“CreatedDateTime”: “2023-03-22T00:00:00Z”
},
{
“EmpID”: 1004,
“EmpName”: “Olivia Jones”,
“Salary”: 90000,
“DateOfBirth”: “1978-12-31”,
“CreatedDateTime”: “2023-04-30T00:00:00Z”
},
{
“EmpID”: 1005,
“EmpName”: “William Brown”,
“Salary”: 59000,
“DateOfBirth”: “1982-09-10”,
“CreatedDateTime”: “2023-05-15T00:00:00Z”
}
]

Order Table:
JSON

[
{
“OrderID”: 1234,
“CustomerID”: 2001,
“OrderDate”: “2023-04-05”,
“OrderStatus”: “Shipped”,
“TotalAmount”: 250.50
},
{
“OrderID”: 5678,
“CustomerID”: 3010,
“OrderDate”: “2023-05-10”,
“OrderStatus”: “Pending”,
“TotalAmount”: 189.99
},
{
“OrderID”: 9012,
“CustomerID”: 2001,
“OrderDate”: “2023-03-15”,
“OrderStatus”: “Completed”,
“TotalAmount”: 421.75
}
]
Product Table:
JSON

[
{
“ProductID”: 1,
“ProductName”: “iPhone 12”,
“Category”: “Electronics”,
“Price”: 999.99,
“InStock”: true
},
{
“ProductID”: 2,
“ProductName”: “Samsung Galaxy S21”,
“Category”: “Electronics”,
“Price”: 899.99,
“InStock”: true
},
{
“ProductID”: 3,
“ProductName”: “Sony PlayStation 5”,
“Category”: “Gaming”,
“Price”: 499.99,
“InStock”: false
}
]
Sales Table:
JSON

[
{
“SaleID”: 1001,
“ProductID”: 1,
“Quantity”: 5,
“SaleDate”: “2023-06-01”,
“TotalAmount”: 4999.95
},
{
“SaleID”: 1002,
“ProductID”: 2,
“Quantity”: 3,
“SaleDate”: “2023-05-20”,
“TotalAmount”: 2699.97
},
{
“SaleID”: 1003,
“ProductID”: 3,
“Quantity”: 2,
“SaleDate”: “2023-04-10”,
“TotalAmount”: 999.98
}
]
Customer Table:
JSON

[
{
“CustomerID”: 2001,
“CustomerName”: “John Smith”,
“Email”: “[email protected]”,
“Address”: “123 Main St”,
“City”: “New York”,
“Country”: “USA”
},
{
“CustomerID”: 3010,
“CustomerName”: “Emily Johnson”,
“Email”: “[email protected]”,
“Address”: “456 Elm St”,
“City”: “Los Angeles”,
“Country”: “USA”
},
{
“CustomerID”: 4050,
“CustomerName”: “Michael Brown”,
“Email”: “[email protected]”,
“Address”: “789 Oak St”,
“City”: “Chicago”,
“Country”: “USA”
}
]

My challenge is that when I attempt to extract and transform the JSONs, Power BI only processes the first JSON and ignores the rest. I need a solution or workaround to ensure that all the JSONs are processed and transformed into separate tables within Power BI.

I would greatly appreciate any guidance, tips, or suggestions on how to handle this issue effectively and ensure that all the JSONs are properly transformed into separate tables based on their unique key-value pairs.

I have tried to transform JSON data into separate tables in Power BI. I used the transform option, which includes the JSON option, on the specified JSON column. By using the base table, I created a reference table in Power BI and used the “Extract Values” option to extract values from the JSON column. However, it only transforms the first JSON and not all of them.