Forum Discussion
Solved
ELT JSON Semi Structured Loop
Good day, Apologies this is a bit of a duplicated but because i only recently have plan approved, so i dont get the time to do deep research or learning as yet Solved: Re: ELT JSON / Semi struct...
- For this scenario I would not use Power BI Desktop as the main ingestion engine.Power BI Desktop / Power Query can handle simple JSON, but it becomes difficult to maintain for complex API loops, pagination, nested JSON, schema drift, retry logic, and Bronze/Silver landing patterns.The pattern is more appropriate:API→ Pipeline / ForEach pagination→ Raw JSON to Lakehouse Files→ Notebook/PySpark to flatten nested JSON→ Silver Delta tables→ Gold Warehouse / semantic model→ Power BI reportPower Query can technically do pagination with functions like List.Generate, but for production-style API ingestion I would use Fabric Pipeline + Notebook instead.Also, dynamic API URLs in Power Query can cause scheduled refresh issues in the Service unless Web.Contents is structured correctly with a static base URL and RelativePath/Query.So the limitation is not “Power BI Desktop cannot read JSON”.The limitation is that Desktop is not a durable orchestration tool for API ingestion and semi-structured ELT.
🔍Parchitect
Solutions Architect · Microsoft Fabric Specialist💡Helpful? Kudos are appreciated.
✔️Solved? Mark as Solution so others can find it faster.
For this scenario I would not use Power BI Desktop as the main ingestion engine.
Power BI Desktop / Power Query can handle simple JSON, but it becomes difficult to maintain for complex API loops, pagination, nested JSON, schema drift, retry logic, and Bronze/Silver landing patterns.
The pattern is more appropriate:
API
→ Pipeline / ForEach pagination
→ Raw JSON to Lakehouse Files
→ Notebook/PySpark to flatten nested JSON
→ Silver Delta tables
→ Gold Warehouse / semantic model
→ Power BI report
Power Query can technically do pagination with functions like List.Generate, but for production-style API ingestion I would use Fabric Pipeline + Notebook instead.
Also, dynamic API URLs in Power Query can cause scheduled refresh issues in the Service unless Web.Contents is structured correctly with a static base URL and RelativePath/Query.
So the limitation is not “Power BI Desktop cannot read JSON”.
The limitation is that Desktop is not a durable orchestration tool for API ingestion and semi-structured ELT.
🔍Parchitect
Solutions Architect · Microsoft Fabric Specialist💡Helpful? Kudos are appreciated.
✔️Solved? Mark as Solution so others can find it faster.
Parchitect Thank You, i was wondering how to do this
