Unlocking Low-Code Wizardry with Power Platform and GitHub CoPilot: Effortless CSV to JSON Conversion powered by Azure Functions

Have you ever found yourself tangled in the web of data conversion challenges? As low-code enthusiasts, we often encounter the need to convert data from one format to another. You might have relied on Power Automate or Power Apps for specific use cases, but what if there's a more versatile and shared solution that works seamlessly across various scenarios?

Join me on a journey as we explore the magic of GitHub CoPilot and Azure Functions, empowering both pro-developers and citizen developers in organizations to effortlessly conquer CSV to JSON conversions. In this blog, we'll not only unravel common Power Automate methods for handling this task but also delve into the power of GitHub CoPilot to create an Azure function that can be harnessed by anyone, irrespective of their coding prowess.

Parsing a CSV with Common Power Automate solutions

Let's embark on our CSV to JSON conversion journey using Power Automate's commonly employed methods. Many of us are familiar with the "Apply to Each JSON" approach or the "Select" method to build arrays that can be converted into readable JSON. While these techniques prove useful, they can be time-consuming and resource-intensive when handling large datasets. Nevertheless, they serve as reliable solutions for most tasks.

Here's a general example of how you can implement these methods within the Power Automate flow:

Despite the successful outcomes, there's an opportunity to explore an even more efficient way to achieve our goal.

CoPilot and Azure Functions

When exploring alternative solutions, I turned to the power of Azure to discover a quick and user-friendly approach that could serve as a shared resource for everyone. Curious about the most efficient method to convert CSV to JSON through code, I reached out to Bing and even consulted ChatGPT. In the end, Python emerged as the language of choice for this task.

Though Python wasn't my familiar territory, inspiration struck me: why not leverage the capabilities of GitHub CoPilot? With Visual Studio Code already at my disposal, I promptly installed GitHub CoPilot and began working with this ingenious AI-powered tool.

The first question on my mind was, "How do I release an Azure function with Python to Azure from Visual Studio Code?" Thankfully, GitHub CoPilot didn't disappoint and provided me with clear instructions:

Following these step-by-step guidelines, I swiftly installed the necessary extensions and laid the foundation for my project within VS Code.

But the real magic happened when I sought CoPilot's assistance in crafting the CSV to JSON conversion code in Python. With a matter of seconds, CoPilot equipped me with an elegant solution:

With my project in place, code at hand, and all the essential resources ready, it was time to deploy the freshly minted Azure Function. Through seamless integration with Co-Pilot's guidance and some initial test sequences, success greeted me in no time.

Calling function from Power Automate

The moment of truth arrived as I put my solution to the test within Power Automate. Opting for a simple HTTP call in my initial trials, I eagerly awaited the results I hoped for. With a smooth setup, the function was successfully invoked after just a couple of attempts.

Upon retrieval, the formatted array still required conversion into a JSON object for seamless interpretation within Power Automate. Yet, the underlying principle remained evident: a streamlined flow with significantly fewer steps, allowing multiple Flows and resources to tap into this conversion powerhouse.

Taking it further

As our solution proves its mettle, the path to ultimate efficiency lies in transforming it into a Custom connector, thus maximizing its widespread adoption throughout the organization. Picture this: with a sleek connector at our disposal, we could effortlessly input the CSV content into the body and receive a seamless JSON response. It's the epitome of efficiency!

Having discussed this possibility with Co-pilot, I gathered valuable insights on how to fine-tune the code to perfect this integration. While the technicalities of crafting a custom connector are best saved for a future post, the promise of an even more streamlined process beckons.