Skip to main content

Power Query DataFlows Connector are like SSIS for the Cloud

I stumbled across the DataFlows connector. And wow! Our organization used a custom javascript query embedded in Stratus Forms (kudos to Mark Rackley for that). The code was querying a third-party website API to pull in information to be collected as a choice option in a SharePoint field. But... custom development sometimes breaks and I'm just speculating but I don't think Mark supports Stratus Forms anymore - making it a tool we can't continue to use. Additionally... we're on our path to using PowerApps and PowerPlatform to replace custom web forms in SharePoint.

I asked myself what connectors I could use to query API data and post them in a PowerApps gallery for use in a form to patch back to a SharePoint list. And I stumbled onto DataFlows.

DataFlows are like the - easy to use - Get Data buttons in Excel and PowerBI. Yet, it creates an encapsulated table that you can pass to any PowerPlatform application. I couldn't believe I had stumbled across something so easy to use from Microsoft.

I've built a lot of SSIS packages and think they are amazing ETL tools but they are chained to on-premises processes. A PC for designing them, servers for storing the information, constant updates to the software, and dependencies on the developer to write in logging for each stage. DataFlows provide a lot of relief to those problems. The cloud-based environment lets me edit from anywhere. I'm not updating the infrastructure. The PowerPlatform has amazing workflow logging and error-catching. DataFlows may not be a full replacement for SSIS but it can do a large amount of what I had used it for in the past with greater convenience.

There are so many unique ways I can imagine using DataFlows:

  • Create custom queries to third-party information to be utilized in things like choice fields for PowerApps
  • Build DataFlow packages for PowerBI report authors
  • Generate Web Parts for SharePoint to display information from other sources
  • Transform disparate data sources from a data lake to bring them into a unified gallery
An initial drawback I encountered from DataFlows was what it did with the data. Since I was playing with the connector and I had never used it before, I did not plan on what I was going to do with the output. Conveniently, Microsoft allows for this and automatically created a new table in my Dataverse environment. This was not ideal. I was immediately scared of all the junk tables that I or any user could create. My brain immediately went to thoughts of duplicate tables, messy connectors, unmanaged connectors, and aberrant connectors hitting unbeknownst throttling limits. Quite a cool tool, with the potential for technical debt and organizational waste if not properly maintained and governed.

I'm definitely a fan of PowerPlatform DataFlows.

Comments

Post a Comment

Popular posts from this blog

How to Create and Use a SharePoint App Registration

SharePoint App Registrations are incredibly useful for unattended tasks to read or update SharePoint sites. It's similar to a user service account but more secure. They cannot be used as a user sign-in. They are used in custom applications or automated scripted processes. They are made even more secure with a certificate, in which case, they may only run from the machine that owns the certificate. SharePoint Things to Do with App Registrations Read data and write the data to another process Write CSV, text, or XML files Useful for data for to import into report software Writing the data to other APIs such as ERPs, Project software, or work authorization systems Changing the data Check the data based on a schedule. Update the data based on constraints Change permissions of items in a SharePoint list once an item is past due Work with partners and vendors to provide just-in-time, specific access to necessary data Simple Process Create the application either in SharePoint or in App Re
Post a Comment
Read more

The Fix: npm ERR! code SELF_SIGNED_CERT_IN_CHAIN [SOLVED]

npm ERR! code SELF_SIGNED_CERT_IN_CHAIN npm ERR! errno SELF_SIGNED_CERT_IN_CHAIN npm ERR! request to https://registry.npmjs.org/gulp-cli failed, reason: self signed certificate in certificate chain What does it even mean? How does one fix this? There are so much information, it's hard to parse what I'm supposed to do. The Solution: Disable the Company VPN This is not ideal and you should work in cooperation with your IT security operations to let them know what you are doing and why you need it. It was reading this article:  https://stackoverflow.com/questions/54611707/request-to-https-registry-npmjs-org-co-failed  and the comments that finally clued me into our VPN causing the conflict. I previously did not have any issues but our company hired a new CISO who has been very active in hardening our corporate environment. What didn't work: gulp untrust-dev-cert Removing package-lock.json file Reinstall Node.js What I wasn't going to try (and you shouldn't either): npm
Post a Comment
Read more

PowerApps Productivity Timer

I like to track activities . I have several workout apps and a Fitbit . Measuring things allows us to monitor our performance and journal our success. But not all activities have ready-made, easy to use apps - a problem measuring things that don’t fit into popular categories. Early in my IT career I was an IT procurement specialist for a large enterprise. Procurement specialists process 20-60 requisitions a day. It’s a lot of paperwork (even with a digital process.) I oversaw making it more digital and less paper based. I succeeded in reducing a typical requisition from 8 minutes to 2 minutes. The process allowed procurement to reduce person hours by almost a whole position. I’ve always said that computers allow us to do more and there was always more to do. Being able to measure that efficiency improvement is the impetus for having an easy-to-use timer. I have learned many times over the years that productivity is lost or gained in the moments between activities. I waste a lot
Post a Comment
Read more