ELT JSON Semi Structured Loop

[icassiem]

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 structured - Microsoft Fabric Community

My source are mostl API JSON that i have to transform but before i can transform i need to loop, i dont know if dataflow is like powerbi powrqury window or i do it in powerbi one example and past the script in dataflow but how would i do api loops then unpivot the json, would i need pythin to first downlad all looped api content then pass on dataflow etc?

Regards

[Parchitect · Accepted solution]

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.

[icassiem]

@Parchitect  Thank You, i was wondering how to do this