Having recently worked-through numerous issues with API data feeds and deployed report configurations, I’ve learned a few important best practices and caveats – at least for some common use cases. In one example, we have a client who expose their software-as-a-service (SaaS) customer data through several web API endpoints. Each SaaS customer has a unique security key which they can use with Power BI, Power Query or Excel and other tools to create reporting solutions. If we need a list of available products, it is a simple matter to create a long URL string consisting of the web address for the endpoint, security key and other parameters; an then just pass this to Power Query as a web data source. However, it’s not quite that easy for non-trivial reporting scenarios.
Thanks to Jamie Mikami from CSG Pro for helping me with the Azure function code for demonstrating this with demo data. Thanks also to Chris Webb who has meticulously covered several facets of API data sources in great detail on his blog, making this process much easier.
Click through for the instructions.