Forum Discussion

icassiem's avatar
icassiem
Icon for Post Prodigy rankPost Prodigy
2 months ago
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 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

Need Help
  • Parchitect's avatar
    Parchitect
    2 months ago
    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.

2 Replies

  • Parchitect's avatar
    Parchitect
    Icon for Solution Sage rankSolution Sage
    2 months ago
    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.