In the far reaches of the cosmic realm known as Azuria, a remarkable journey began. It's a tale of discovery and transformation, illustrating the hidden potential within Microsoft Power Apps. This is not just a story, but a portal to a world where a humble spreadsheet can evolve into a dynamic web-connected mobile app with the assistance of professional developers. As we embark on this fictional quest, remember that the magic I describe mirrors real possibilities.
Meet Phillip, a squire in the remote kingdom of Azuria. Just like a normal squire, Phillip has many daily tasks including polishing the armor, cleaning up after the great battle steed Excel'erant, and sharpening the weapons of his knight, Sir Pow R. Platformius. Sir Plaformius is a noble knight in the service of the king of Azuria, but he keeps his young squire on his toes with his never-ending demand for a questionably-specific list of snacks prescribed by the kingdom's wizard, J'son Retriever.
The wizard J'son Retriever is not only a professional developer in the dark arts of C# but is widely considered the medical expert in the kingdom. Here is the list of snack products prescribed for Sir Platformius to enhance the great knight's battle stamina in the cosmic wars of Azuria:
- Oreo cookies
- Pringles potato snacks
- Doritos Cool Ranch chips
- Original Ritz crackers
- Cinnamon Toast Crunch cereal
- Skinny Pop Sea Salt & Pepper Popcorn
- Cascadian Farm Organic Cinnamon Crunch Cereal
- Cocoa Puffs cereal
- Pepperidge Farm Goldfish Cheddar crackers
- Jack Link's Beef Jerky Original
Though modern medical science might venture to raise a brow at the questionable contents of this query, it is, nonetheless, Phillip's solemn duty to stock these items in Excel'erant's saddlebags.
Late one evening poor Phillip sallied out on an errand run to a store on the far side of the northern boundary of Azuria. The old storekeeper was officially closed by the time poor Phillip arrived, but the man took pity on the boy and opened the store so that Phillip could collect the required boodle. As Phillip passed the payment to the old man, the storekeeper asked Phillip why he'd ventured so far from the castle for these items. Phillip replied that he simply knew of no other place to get the items at that hour of the day. The old man winked at Phillip and passed him a spreadsheet that he kindly put together whilst Phillip was gathering his grocery list items. The list was an abbreviated portion of the storekeeper's own inventory list, but it had columns including product names, UPC codes, manufacturers' websites, some JSON information on stores, and the cost of the items. You can view what it looked like in Figure 1.
The storekeeper suggested to the boy that using this Excel spreadsheet, he could build a Power Apps app that he could use on his mobile device and effectively eliminate his evening excursions. The old man told Phillip that he could use such an app to not only track, but also to learn more about the product and even place an order, without even visiting the storekeeper! Although, the storekeeper admitted, this was a bit beyond the abilities of a simple citizen developer like Phillip, if he could get the assistance of a professional programmer, he could certainly achieve all these things. Phillip stood aghast! Never had he considered such an endeavor, but he excitedly left the storekeeper and returned to the castle.
Easy App Building From Excel
Microsoft Power Apps provides developers with the ability to start an app with any available data. This could be a SharePoint list, a Dataverse table, a comma-separated values file, or an Excel spreadsheet. Within a few clicks of a mouse, you can easily create an app based on your data. Power Apps is smart enough to take data from any spreadsheet and the data does not even need to be in table format. In Phillip's case he will use the simple Excel spreadsheet without a table that the storekeeper gave him.
The next morning, after Phillip completed his tasks, including refilling Excel'erant's saddle bags, he sat down at his computer and opened the Power Apps Maker Portal from his PC. Seeing the option to Start with data right from the Home screen, he selected that option, and to his astonishment, he saw the option to Upload an Excel file. Once Phillip uploaded the shopkeeper's Excel file, he watched as Power Apps transformed his data and displayed a table like Figure 2 showing the spreadsheet's data.
When Power Apps begins transforming incoming data like this, it uses Copilot to analyze the type of data that it is importing. In the preview screen, resembling the Microsoft Power Query, the developer can shape the data before it becomes a Dataverse table, simply by selecting the column headers. For example, if you have a numeric column that would be better sorted as a text column, you can edit the column to display text as the data type. You can also rename the table of data to whatever you want. In our example, Phillip needs to change some numeric columns to text and rename the table.
After examining the proposed table presented by Power Apps, Phillip noticed that the UPC Code column looked a little off, and he had warnings for a couple of his items, so he selected the column header and corrected the formatting to make it a text column. He did the same with the SKU column. Phillip wasn't too certain what to do about the JSON column, so he left it as text. He also changed the Manufacturer URL column to be of type URL. Satisfied that he had the data how he wanted, Phillip simply selected the Create app button. After a few moments, he had a canvas app (like Figure 3) with all J'son's prescribed items on the left of his screen, and a form with details of the selected item on the right of the screen. Phillip did no coding until this point, he just looked at a screen of data and asked Power Apps to make the app for him. With a quick adjustment of the fields in the gallery on the left side of the screen and the title at the top of the screen, Phillip got the look that he was looking for.
As I mentioned, Power Apps creates a Dataverse table when it builds an app like this. If you need to adjust your data after you've built the app, you can access the data table from the home screen of the Power Apps maker portal by selecting it from the Tables list. I can't say enough about the ability to transform your data before you import it. Phillip now has a very usable, automatically responsive app by merely importing data.
Phillip tugged his leather-bound ledger from his pocket and compared the numbers. He discovered that he could quickly make changes to the saddle bag quantities with his new app. Phillip found this new way to input and adjust data quite helpful, but he really wanted a faster way to look up items with his mobile device. He realized that he needed quick access to answers to Sir Platformius's questions about the snacks, and that meant he must have the ability to find information from the internet inside of his app.
Adding a Barcode Reader
Phillip remembered a recent visit to a restaurant where he scanned a barcode from his mobile phone to pay his bill. He wondered, “Can my app link to information about the product?” He looked through the available controls, searching for a barcode reader and found one. He added a barcode and tested it. He saw that each time that he scanned an item, Power Apps collected the type of barcode and the value of the barcode in text format. However, a barcode value just wasn't that helpful on its own.
Phillip adjusted the OnScan property of the barcode reader to have it dynamically lookup the gallery item, and that got him a step closer. Instead of scrolling through his list or searching for an item, the barcode scanner took him to the item that he needed. However, he still had no idea how to look up information beyond what was in his data.
The barcode reader is one of the most powerful features of Power Apps for both professional developers and makers alike. Power Apps natively provides the ability to interact with the device without writing any traditional code. Power Apps excels at referencing your device's GPS coordinates, barcode and NFC scanners, the camera, and more. Using these built-in features in your applications lets you unlock powers you would not have thought possible.
Finding an API
Phillip pondered how he could look up information about his items in the app. He used a quick Bing search for a barcode UPC API and found some options. Looking at the examples, Phillip found one that would suit his app, but he had no idea how to use it. The examples retrieved some JSON code that was exactly the kind of information that he was looking for. However, Phillip had no idea how to use the JSON code, not to mention the information about the Stores that was in JSON format that was already in his data. At this point, Phillip knew he was at the end of his abilities, so he decided to ask the wizard J'son for help.
Enlisting Pro Dev Help
When a citizen developer reaches the end of their ability, that's by no means the end of the story. This is exactly the point where a professional developer can assist. Giving Power Apps the ability to pull in and translate JSON code is where the low code bridges to high code. Phillip told the wizard J'son what he was trying to do, both in looking up data and in translating the JSON code within his data.
The first thing Phillip asked the wizard to do was to build a custom connector to get the data based on an item's UPC. To get started, the wizard opened the Power Apps maker portal to look for a link to build a custom connection. After some searching in the left-side rail navigation menu, he selected the More > Discover all, and within a pane titled Data was the piece that he was looking for: the Custom connectors section. J'son easily found the link to create a new custom connector at the top of the Custom connectors screen and he was able to pick Create from blank from the dropdown as shown in Figure 4.
Why Build a Custom Connector?
Microsoft Power Apps has over 1,000 pre-built data connectors that a developer can use. So, why would you choose to build a custom connector? If none of the existing connectors can call the APIs, services, or systems that you're looking for, you can build one with your own triggers and actions. Power Apps includes a custom connector wizard that starts when you select New custom connector from the Custom connectors page. For the professional developer, this is a convenient way to input what's needed to connect to the data. Once you complete the new connector, you can share it with your app makers. Then the app maker can add it to their app the same way they would add a connection to Dataverse or Office 365 Outlook.
Custom Connector Added
The wizard completed the new connector and shared the connector with Phillip. The lad then went back to his app and easily added it as a data source. Next, he added a button to his form header and modified the OnSelect function to use the brand-new connector to pull in the product information of the form item and save it as a variable. He created a second screen to display the product details, and he added some labels and an image control to display the data that he wanted. He also added a Navigate function at the end of his product details button's OnSelect code to send him to the product details screen, like Figure 5. That would do it!
Happily, Phillip discovered that by selecting his new button, he could bring in the same data that he could by entering a URL in his browser, only now the data showed up inside of his app! He could see the information about where he could purchase the product, the cost to purchase the item and even where to buy the product on-line. Everything he needed, almost. But he still needed to show the manufacturer's web page on his details screen.
Use a Custom-built Control From PCF Gallery
Let's discuss the requirement about the manufacturer's web page showing in the app. We have a URL from our Excel data we imported into Dataverse, but how can we use that as a link and display that web page inside of the app? Out-of-the-box, Power Apps doesn't have a built-in control to do that. Boo. Never fear though, we can work with our pro dev to build us a Power Apps component framework (PCF) control.
The Power Apps community is an ever-growing resource of information, and you can find a repository of custom-built controls in the PCF Gallery. However, without some programming experience, a citizen developer will struggle to take advantage of this resource. A professional developer can bring in the component as a downloadable zip file from GitHub. Using TypeScript for the core logic and HTML/CSS for the user interface, you can make the necessary tweaks in Visual Studio Code. Once complete with your modifications, you can create a zip file from the solution product. With the zipped file, you can import it into the Microsoft environment that you're building in, much like they did with the Custom Connector. At that point, it becomes a usable resource for your app developer.
Remember, before you try to develop your own PCF control check out PCF Gallery. There are a lot of community controls out there already that you can leverage, and their code is on GitHub so you can see their inner workings. Once you learn how these things work you can start to contribute to PCF Gallery and make the community even stronger.
Add a Custom-built Control
To use a custom-built control in an app, we need to use the Insert menu tab from the left side rail. Way down at the bottom of the tab is a Get more components link. Selecting the link brings up your Import components pane. To find the custom-built control, you need to select the Code tab where you can select the control you want to insert in your app (see Figure 6). Once inserted, your Insert menu will display Code components where you can find any added custom components.
Phillip Uses the Custom Control
With the new control added to his details screen, Phillip saw the new control display an actual web page from Microsoft Bing. He resized and repositioned the control. Then he adjusted the source property of the new control using a PowerFx formula to update from the selected gallery item's Manufacturer URL column, and voila, he had an interactive browser inside of his app! The next thing he needed was the ability to translate that JSON code in that StoresJSON field from his original data. He knew he needed help from the wizard J'son yet again.
The ParseJSON Function
Developers can use actual JSON code as a data source inside of the app. To a pro dev, this sounds undaunting, but to a citizen developer, this can be a challenge. Microsoft has a function called ParseJSON to convert JSON into an Untyped object, and the developer can then declare what data type they want it to be. Once the developer completes the translation, they can take that data and use it within the app. We specifically created a JSON column in our sample data for Phillip's app so we could demonstrate how this works.
Our JSON string includes an item called StoreDetails and within these details is a table including multiple records including the store name, the city, and the state. In the form beside of our gallery, if we want to show the store name information in a label, we must pull the table of store names from the StoreDetails JSON information, and then wrap it in a Concat function with a semi-colon separator. Additionally, we use an As function so we don't have to duplicate the code in the third line of our statement. Remember, as I always say, Power Apps wants what Power Apps wants. The label field in our StoresJSON card is expecting text, so that's what we must get it. If we didn't use Concat, we would upset Power Apps because it would have a table instead of text. You can view our final formula in Figure 7.
Compare that nice, easy to read formatted list of stores to the JSON field that it came from (Figure 8).
This is a very powerful function in Power Apps when put in the right hands. No one wants to look at all those quotes and curly brackets, but with some simple Power FX you can transform it anyway you want based on the data type.
Better With Pro Dev
The point of Power Apps is to make app building accessible at every level of your organization. It is very easy to build an app on existing data to view and modify the data. You can make apps responsive to any platform and mobile, taking advantage of features like phone cameras. However, you can really take a Power App to the next level of sophistication with the help of a professional developer.
As we walked through the app in our example, we added a custom connector to bring in an online data source, we added a custom control to display a web page, and we introduced how you can use JSON coding in your app. Power Apps is an excellent way to interact with data from multiple sources inside of a single app. So, after all this, I hope that you now have some motivation and interest in assisting your organization's citizen developers, or even building a Power Apps solution on your own, like Phillip. To learn more about building controllers, see David Yack's article, "Power Platform ❤ Code Developers".
The app that Phillip constructed with J'son's help really impressed Sir Platformius. He saw that his squire was more energetic, since Phillip was not out scrounging for supplies late in the day. Phillip also impressed the wizard J'son with what he could do with his Power Apps, so much so that J'son requested some time each week to apprentice Phillip in the darker arts of C#. Phillip also remembered the storekeeper by submitting regular orders early in the day, so that delivery mules could bring the supplies to the castle before evening. Sir Platformius found another squire to clean up after Excel'erant, and titled Phillip: Squire, First Class, and everyone slept soundly that night.
