Employee Check-in Simplified using Power Automate

Company employees who move between offices are often faced with a tedious process of having to check-in at the security desk to get a temporary pass.  This process can be time consuming and is further exacerbated when the person who needs to approve the check-in is not available.  Luckily, we can use flow to make our life much easier.

The premise of this flow is that for each location of a company, when an employee gets close to an office, an email would be sent from the corporate address to the security desk with information about the employee indicating that a pass should be provided and identifying them.  Here's how you can built it.


Create a Custom SharePoint list that includes the following fields
  • Title - name of the location
  • Latitude
  • Longitude
  • Security Email - the persons who will receive the email with the request for access
  • Instructions - some text to include in the email about the access
  • City - the location of the office.  The data must match the city names in the employee's Office 365 profiles

Power Automate

There are two flows that make up this solution.  Trigger flows (in green) are replicated for each office location, while the main flow is shared.

Location Trigger Flow

The trigger flows leverage the experimental When I enter or exit an area location trigger.  When you create the flow, record the longitude and latitude of your location.  Notice that each coordinate is made up of six decimal places.  

Now, create a new entry in the SharePoint list.  Give it a name in the Title field, the name of the City, an email for the Security Email that will receive a notification, and optional instructions.  For the Longitude and Latitude, enter the first 5 decimal places and truncate the rest (don't round the numbers).  

This flow will be triggered when an employee either enters or leaves the area defined by the radius in the trigger.  In our case, we only want the main workflow to run when the employee enters the area (transitionType equals 1).  In that case, the user principal name, latitude, and longitude are passed to the second Flow.

Main Flow

When the main flow is triggered, then we know which employee (by UPN) entered and the location they entered into.  So, we look up the location of the in SharePoint and return the item.  Remember in the first Flow that I mentioned that only the first 5 decimal places should be saved?  Here's why - when you create a Number field in SharePoint, you can only specify up to five decimal places (this holds true in the Classic and Modern experience)

So, to avoid situations of rounding and missing the exact address, I am limiting the decimal places to 5.  Here's how

Multiply the float by 100000 and convert to string43.68293856"4368293.856"
Retrieve the sub-string before the period"4368293.856""4368293"
if(equals( IndexOf(outputs('Multiply_latitude_by_100,000_and_convert_to_string'),'.'),-1), outputs('Multiply_latitude_by_100,000_and_convert_to_string'), substring(outputs('Multiply_latitude_by_100,000_and_convert_to_string'),0, IndexOf(outputs('Multiply_latitude_by_100,000_and_convert_to_string'),'.')))
Convert string to float and divide by 100000"4368293" 4368293

At this point, we have coordinates that should match exactly what is in SharePoint.

Next, we look up the items in the list with the same coordinates bu non-matching cities.  The latter condition will ensure that an employee is not generating emails to their own security email each they come to their own office.  The information in the items is used to notify the front desk security for that location.

Build you own Raffle Solution using the Power Platform and more

If you're a presenter or organize events, then you may agree that one thing that always attracts an audience are raffle prizes.  In the past, I used to ask users random questions in the audience, which works great, but given that most of my presentations in the past couple of years have been in the Office 365 space, I thought it would be a great idea to build an app for it.  This particular app uses a number of technologies - Microsoft Forms, Flow, PowerApps, SharePoint, and and IoT button.

Solution Overview

The way the app works is that my attendees will register to my event using a QR code (try it).
The QR code will take the attendees to a Microsoft Form, where all they provide is their name and email address.  Once submitted, their name is entered into a SharePoint list.  When the app is started, it periodically checks for any new submissions (in case anyone wanted to enter the raffle at the end of my session).  I then use a Flic IoT button to trigger when the app starts to run through the list of raffle registrants and display their names.  When I click the button a second time, the timing between each name increases (like a slot machine) and eventually stops with a great display of fireworks.  A third click of the button will clear the name and start the process over.  Let's see how I built it.

Solution Design

Microsoft Forms

For this solution, I created a simple Form that you can access via the QR code.  I change the title and logo of the form based on the event I'm presenting at.

SharePoint List

To capture the attendees names, I use a Custom List with 2 fields in it
  • Title - Single Line of Text, mandatory
  • Email - Single Line of Text, optional
The email field is not required for this app.  I'm capturing it to send a courtesy email to the attendee thanking them for attending my session and providing them with my contact information.


I built the app as a Canvas app.  The look and feel is very flexible.  Let's look at some of the specific needed to run the solution.  I have used several controls as the core of the solution:

  • imgCongratulations - that is the image (animated GIF in my case) that appears when the counter stops and the winner is announced
  • Timer1 - when this timer ends, it will iterate through the name
  • flicTimer - calls a Flow to determine if the button has been pressed 
  • attendeeTimer - this timer will periodically refresh the list of attendees from SharePoint
There are a number of companies selling such IoT button, including Flic (by Shortcut Labs) and bttn.  If you don't have a button, you can always adjust the solution to use buttons in your App.  Now, let's discuss the customization of these controls


When the app first loads, I load the participants from the SharePoint list into the participants collection.  There are also a number of variables I set:
  • index - index of the attendee in my collection
  • dur - duration between name changes in the raffle (in milliseconds)
  • stopping - indicates whether the button has been pressed a second time to slow down and stop the raffle
  • refreshCounter - indicates whether the Dur counter was refreshed
  • winner - winner name
  • clicked - response from Flow on whether the Flic button has been clicked
OnVisible:  ClearCollect(participants,'Participants'.Title);


In order for the image to appear, two things need to occur.
  • The state needs to be set to 2 - stopping
  • The duration needs to be set to 1000 - indicating that it's no longer changing
Visible:  (2=state) && (1000 = dur)


This timer triggers every second and refreshes the list of attendees from the SharePoint list.

Autostart:  true
Duration:  1000
Repeat:  true
OnTimerStart:  Refresh('Participants');ClearCollect(participants,'Participants'.Title)


The flicTimer is used to send requests to a Flow and wait for a response.  Depending on the state that the app is in, the action of the button will have different impacts.
  • state = 0 - this is the initial state.  The names are not shown and the index of the participants array is set to the beginning.  As well, the duration is set to 50 ms for quick updating.  Stopping, which will slow down the display of the names is also set to false.
  • state = 1 - this is the state, where the names are being displayed in 50 ms intervals.
  • state = 2 - in this state, the display of the names slows down from 50 ms to 1000 ms intervals and eventually stops.  Once stopped, imgCongratulations is displayed
The timer is set to 12 sec so that there wont be too many calls being made to the Flow at once.

Autostart:  true
Duration:  12000
Repeat:  true
OnTimerEnd:  UpdateContext({clicked: GetFlicButton.Run().response});
    clicked = "clicked",
    If(state = 0,
       UpdateContext({startTimer: true,stopping: false,index: 0,dur: 50,refreshCounter: false,state: 1}),
     If(state = 1,UpdateContext({stopping: true,state: 2}),
        UpdateContext({index: 0,dur: 50,stopping: false,startTimer: false,refreshCounter: true,winner: "",state: 0}))));


This is where the magic happens.  The timer is triggered in state 2, once the button has been clicked for a second time.  As you can see, the duration in this timer is set to the dur variable, which changes as the timer is clicked.  As long as the duration is less than 1000 ms, the timer will continue to run.  Once it hits 1000 ms, it will stop and the winner variable is set to the first name of the item in the SharePoint list.

Autostart:  false
Duration:  dur
Repeat:  true
Start:   If(dur < 1000,startTimer,false)
OnTimerEnd:  If(index = CountRows(participants)-1, UpdateContext({index:0}),UpdateContext({index:index+1}));


There are two Flows that are used in this solution.  The first one captures the Form submission and stores them into a SharePoint list and send out a courtesy email to the attendee.

For the second Flow, I used the approach discussed in my previous article Adding a Flic button trigger to a Flow triggered by PowerApps.  In essence, I trigger the Flow from my PowerApp and wait for a button click.  If the button is clicked, the the flow returns clicked.  Otherwise, it returns nothing.  This is the value that is stored in the clicked variable described in the PowerApp screen description above.

Save time building Flows using Copy and Paste for your Actions

Microsoft just release the preview some long-awaited functionality that allows users to easily copy and paste actions for re-use.  This functionality will save makers significant amount of time when reusing actions within a Flow or across different Flows.

Copying Actions

To copy an action to the clipboard, all you need to do is click the ellipses and select Copy to my clipboard.

Once an action has been saved, you can add it as a new step by selecting My clipboard from the action chooser and clicking the action you want to paste.

You can even have a peek at the code by hovering on top of the (i) to the right of the action name

Save even more time with scopes

Scopes are collections of actions.  They are great if you want to move around a group of actions without having to move one by one.  With the addition of the Copy to my clipboard functionality, you are now able to copy an entire group of actions.  The caveat here is that variables cannot be initialized inside a scope.  So you have to be careful when you want to paste a scope somewhere within the Flow before the variables it's referring to are declared.

Copy actions across Flows

Yes, you read correctly!  The clipboard works across Flows as well.  But as with the scopes, you need to carefully review the copied actions.  If you are consistent with your naming convention, you may save some time here, as they may include references to dynamic content not found in the new Flow.

Clearing the Clipboard

The copied actions remain in the clipboard until you exit Flow completely. 

Note that this functionality is still in Preview, but will undoubtedly change the way we create Flows!

Building Enterprise-wide Multilingual PowerApps

Supporting multiple languages for corporations is a reality. It may be an organization operating in multilingual countries, such as Canada, Belgium, Italy, or others, or having a workforce that that is distributed across the globe. One common challenge in building solutions to support multilingualism is the creation and maintenance of such solutions as they evolve and more languages are added. In this article, I'll be discussing an architecture to create enterprise-wide multilingual PowerApps.

The challenges that are often encountered with multilingual solutions stem from the fact that business needs evolve over time.  This could mean that an existing application needs to be altered to include additional content.  Another common need that arises is that a new language may have to be added to an application.

Back in 2017, Anh-Thu Chang wrote a blog on Building multilingual apps in PowerApps.  Some of the things discussed in this article relate to her blog post and build on the general concept.


The enterprise-wide solution proposed in this article will address key elements for creating an architecture that can support a number of languages across all PowerApps with an organization.  These elements include:
  • Language Determination
  • Central Label Repository
  • Central List Repository
  • Performance

Language Determination

There are numerous ways to change languages in Office 365 as described in my previous article, Working in different languages in Office 365.  For the solution described in this article, I'm using the Office 365 Account settings for the user.  By doing so, I'm able to access a users preferred language in PowerApps using the Office 365 Users connector. 

Language settings are made up of two components.  The first two letters, representing the language, a hyphen, and then two more letters representing the locale.  For example, English for Canada is represented as en-ca, while English for US is en-us.  In my case, I only care about the language and not the locale.

If you choose to capture both, you can definitely do so.  However, keep in mind that users within the same region may not pay attention to the locale and only pick the language.  If that is the case, then they may not experience the PowerApp in the language that they expect to.

When the app is launched, the following expression is added to the OnStart() property for the app:

Set(preferredLanguage, Coalesce( Left(Office365Users.MyProfileV2().preferredLanguage, 2), "en"));

The expression gets the user's preferred language from their Office 365 account setting.  It picks the two left-most characters and stores it in a global variable called preferredLanguage.  I use the Coalesce expression to set the default to English (en) to avoid situations, where a user does not pick a language or other reasons that the preferredLanguage may be empty.

Central Label Repository

The Central Label Repository is used to store all the strings that are used across all the apps.  For simplicity of management, I have chosen to use a SharePoint list to achieve this.  If you go down this route, you need to make sure that all your users will have at least read access to this list. 

There are four pieces of information that are needed for each label:

  • Title - a group for the label.  For this, I typically use the App name
  • Field Name - the name of the control that this label applies to.  
  • Label - what is the string that should actually appear
  • Lang - the two-letter language code

Reuse of Labels

There are often labels that are commonly used across numerous forms, such as OK, Cancel, Yes, No, etc.  Rather than defining them for each form, you can create a general group and put them in there.  This will ensure consistency between the labels used across the various forms and reduce the overall SharePoint list size.

Loading the labels

After the preferred language has been determined, each app needs to load the string labels into its memory.  This is done after the preferredLanguage has been determined.  The expression below loads the strings from the SharePoint list into a collection called StringLabels.  

ClearCollect(StringLabels, GetStringLabels.Run("Update My Profile", preferredLanguage)); 

Loading of the strings from SharePoint into the app can be done by either loading the entire SharePoint list and then filtering the values during run-time or using a Flow to filter out the specific labels required and returning those to the PowerApp.  When the list of items gets long, as in the case of defining labels for an entire organization, loading and filtering the entire list within the app may have an implication on performance.  This is why I elected to go with the latter method.  In the expression shown above, I have a Flow called Get String Labels to which I pass the name of the label group (e.g. Update My Profile) and the language.  The Flow then filters the list on these two columns and returns all relevant items

Setting Control Labels

Once the labels have been loaded into a collection, you can now use them to set the text of your controls.  For example, if I have a label called lblCity, then I set the Text property as follows:

Coalesce(LookUp(StringLabels,FieldName = "lblCity",Label), "lblCity")

The Lookup expression searches for an item, where the FieldName is lblCity and returns the Label value.  Coalesce is used to provide a default value in case the label has not been defined for the specific language.  In my case, I make the default Coalesce value the same name of the label as it makes it easier to visually know what labels are missing in my SharePoint list.

Central List Repository

Similar to the central label repository for static labels, you can create a SharePoint list that will include related, such as in drop down lists, radio buttons, and other collection.  The values captured for each list entry are a bit different from the labels:
  • Title - a group for the list item
  • Field Name - the name of the control that this list applies to 
  • Label - what is the string that should actually appear for the list entry
  • Field Order - in what order should the labels appear in the app
  • Lang - the two-letter language code

Loading the list items

Loading o the list items occurs in a way similar to the labels.  However, each group of items is stored in its own collection.  The expression below loads the options for rooms with curtain rods for a drop down list called drpCurtains.  

ClearCollect(drpCurtains,GetListItems.Run("Account", "drpCurtains", preferredLanguage));

Here, I use a similar Flow that filters the list based on the app, list field, and preferred language and sorts the results based on the FieldOrder

Setting List Items

Once the list items have been loaded loaded into a collection, all you need to do is make that collection the source of your list control.  For my drpCurtain field, the :


The Distinct ensure that there are no duplicate fields in my list.  Label indicates that from within the collection, the Label field values should be displayed in the list.


I have implemented the architecture described above in an organization that uses around 40 different forms with over 2,000 labels and 15,000 list values successfully.  There are, however opportunities to improve the performance.  Labels and list items are elements that typically don't change too often.  Therefore, rather than loading the values from a SharePoint list each time the app is opened, it would be better to first perform a query to see if values have changed.  If there were changes, then the labels and list items should be loaded from SharePoint and also stored in the app's offline cache.  If labels have not changed, then the labels and list items should be loaded from the app's cache. 

One way to implement such a check is to keep a timestamp of the last update for the list and before loading the labels check if there were any additions or changes before that date.


The need for multilingual solutions is increasing as organizations are expanding.  With the proliferation of business productivity solutions, the need to provide a localized user experience is also growing.  I hope this article provided you with a good overview on making your enterprise PowerApps a multilingual success.

Adding a Flic button trigger to a Flow triggered by PowerApps

When building PowerApps, you can use Flow to perform a lot of actions that may be more complex than what you can or want to do directly within the PowerApp.  Flows are invoked via any a number of imperative properties of your PowerApp, such as

  • OnStart() - App
  • OnVisible(), OnHidden() - Screen
  • OnSelect() - Button, Text, Label, Drop down
  • OnCheck(), OnUncheck() - Checkbox
  • and more
When you connect a Flow to these controls' properties, then the Flow gets triggered when that action is performed.  For example, when you press a button or label, check or uncheck a Checkbox, or launch the PowerApp.  In essence, PowerApp itself provides the trigger to all these Flows.  

However, there are time, where you want something in PowerApp to occur when there is an external trigger.  For example, you want to update information in your PowerApp when a Flic IoT button is pressed.

The way this can be accomplished is using a Timer control in your App and a dual-trigger Flow.  Here's an example of how I built it..

Timer Control

The timer control, flicTimer, is responsible to do three things:
  1. Call the Flow
  2. Wait
  3. When completed, perform an action inside the App and restart
Here's a description of the property settings I've used:

Autostarttrueunless you need to manually invoke the listener Flow, start automatically
Duration12000make it long enough so that it wont time out, but not too short to cause multiple Flows to fire
OnTimerStartUpdateContext({clicked: GetFlicButton.Run().response});
If(clicked = "clicked", <do something>);
here's where you call the Flow and wait for a response. Once the Flow returned, I would perform a specific action
Repeattrueif the Flow didn't finish in time, repeat the timer


The Flow in this case is fairly simplistic.  It begins with the PowerApp triggering it.  Once triggered, the secondary trigger, When a Flic is pressed, is called to wait until the button is pressed.  There are two possible outcomes to this:
  1.  The button was pressed.  In that case, the branch on the right is executed and returns the "clicked" response to the PowerApp.
  2. The button was not pressed.  Here, the Flow will eventually time out and return an empty result to PowerApp.  

A similar logic can be applied to other types of triggers that may not be easily accessible within your PowerApp.

Blanking out date fields in Dynamics 365 using a Flow

Dynamics 365 supports a number of base content types naively that can be set via Flow.  However, date fields are a bit finicky.  When trying to set a date field, you can provide the date in a string format.  But what do you do if you need to clear out an existing value in a date field in a Dynamics 365 record?

Turns out that the Dynamics 365 connector is not behaving the way most of us would expect.  Leaving the field blank will not change the value.  If you add any value into the date field other than a date field, the action will fail.  

The way to do it is by using the following steps:
  1. Add a Convert time zone action.  If you're not actually converting the time, then set the Source time zone and Destination time zone to be the same.
  2. Assign the Base Time you want for the field.  If you want to clear it out, set it to the null expression.  null will cause the time conversion operation to fail, but still creates a time object.
  3. In the action immediately following the Convert time zone action, set the Configure run after to run after success and failure.

You will need to do this for every date field that you may want to reset.

Building Responsive(ish) PowerApps

Providing users with the ability to access content on their mobile devices has become common place and somewhat of a necessity today.  In the best case, it would be good to have a single solution that can serve multiple devices and orientations.  This article discusses how you can achieve this for PowerApps.

App Settings

Screen size + orientation

Start with creating a PowerApp Canvas App using a Phone layout and orient it to landscape.  On the Screen size + orientation layout page, make sure you disable Scale to fit.  Disabling this setting will allow your app controls to remain the same size regardless of the resolution.  Also disable the Lock orientation so your app will work in both portrait and landscape orientation.  You will notice near the top of the page that the resolution of the app is set to 1136 x 640.   Remember this number as we'll get back to it soon.

Advanced Settings

Next, go to the Advanced Settings tab and enable the Try the enhanced Group control.  Not only does this control help with accessibility, but it also allows you to position your controls on the screen  as a group.

At this point, you will be able to build an app that will have consistent component sizes.  On to the fun part. 

Dealing with screen size and orientation

Each screen has two properties that can be used to determine its size and orientation - Width and Height

Determining how to deal with screen sizes

When you query the Width and Height, it's easy to determine what orientation you are in.  If the Height is greater than the Width, then the orientation is portrait.  Otherwise, it's landscape.  For simplicity, you can store the orientation in a context variable for reference.

If(Screen1.Width < Screen1.Height,

Now to the device and screen size.  Responsive design uses something called Break Points and Media Queries to determine how to render a web page based on the browser size.  We can do something similar in PowerApps.  In my example, I decided that anything with a Width or Height greater than 2000 should be considered a tablet while anything smaller is a phone.  The exception is that if the width is exactly 1136 and the height is 640 then it's a Desktop.  The reason for this last scenario is that PowerApps renders the app using these dimensions in the browser.  So, I determined the device type as follows:

If(Or(Screen1.Width > 2000,Screen1.Height > 2000),
If(And(Screen1.Width = 1136,Screen1.Height = 640),UpdateContext({device:"Desktop"}),UpdateContext({device:"phone"}))

This approach is conceptual and you can define other rules to determine how the app should render. 

What happens when a device changes orientation

So far, I've discussed how to determine the orientation and device type.  But when are these determined?  One way is to set them using the OnVisible property for the screen.  This will work, but will not change when the phone or tablet is changing orientation.  What you can do to address this is add a timer to your screen and add these settings to the OnTimerEnd property.  Set the Duration to be short (e.g. 100 ms) and make sure the AutoStart and Repeat properties are set to true so that the timer will begin as soon as the screen is displayed and will continue to check these settings.

Organizing your controls on a screen

Group components

Earlier in the article, I mentioned that the experimental Group components control should be used.  This control is great for developing responsive apps as it allows you to easily move groups of controls around on the screen rather than treating each one separately.

Make everything relative

Every control has a number of properties that are used to determine where it is positioned on a screen and how big it is.  As well, some controls that are text based have settings to indicate the font family, size, and weight.   When you add controls to a screen, these settings have default fixed values.  In order to allow you to better control the overall layout of your app, its better to use relative values.  This way, when one control changes, all other controls relative to it will change.  For example, in the image below, I am forcing the left group control to be 10px from the top and left of the screen border.  Likewise, I'm setting the Device and Screen Width labels to be 10px from the top and left inside their Group components controls.  All other controls are set to be spaced 10px apart across and down the page.  

For the Group components control on the right, I'm determining its location based on the orientation.  If it's a landscape orientation, then I set the X positions to be 10px after the end of the right Group components control.  Otherwise, I set it at 10px and below the right group control.  

So, for the example above, the Group component on the left is grp1 and the one on the right is grp2.  The positions for grp2 are determined as follows:

  • X: If(orientation = "portrait",10,grp1.X+grp1.Width+10)
  • Y:  If(orientation = "portrait",grp1.Y+grp1.Height+10,10)
Building on this approach, I also defined context variables for the font size and control widths and heights so that smaller devices will have larger fonts and boxes to make them easier to read.  The full expressions for the resizing in my case are:

If(Screen1.Width < Screen1.Height,
If(Or(Screen1.Width > 2000,Screen1.Height > 2000),
If(And(Screen1.Width = 1136,Screen1.Height = 640),


Using the techniques discussed above can, you can build apps that will resize their controls based on the device used and the way it's oriented.  The approach can be further expanded on to show/hide certain controls.

Working in different languages in Office 365

Sometimes, there's a need to work in a different language.  Often, the driving factor is to support  users in different locales.  So, how do you configure the language in for your favourite Office 365 apps?  Turns out the answer is not so simple.

In my tests, I have narrowed down the language configuration to four areas:
To see where things are set, I have configured my environment as follows:
  • Operating System - English
  • Browser - Spanish
  • Office 365 - Hebrew
  • Delve - German
While not exhaustive, I have summarized my findings of which configuration sets the language for which app below.

App OS Browser Office 365 Delve
Admin Center Yes
Delve Yes Yes
PowerApps Studio Yes
PowerApps Language() Yes
SharePoint Site Collections (Default) Yes Yes
SharePoint sub sites (default) Yes Yes
Excel Online Intro Page Yes
Excel Online Workbook Yes
Word Online Intro Page Yes
Word Online Document Yes
Yammer Yes
Teams Online Yes Yes
Teams App Yes
OneNote Online Intro Page Yes
OneNote Online Document Yes
Outlook Online Yes Yes
OneDrive for Business Yes
Flow Studio Yes Yes
Planner Yes
Calendar Yes
Staffhub Yes
My Analytics Yes


There are a few considerations to mention around the language selection that may impact how you manage languages for your needs.

SharePoint Site Languages

When you create a new site collection, by default, no languages are selected and the site defaults to the Operating System language.  However, when a sub site is created, all languages are by default selected.

Cascading Settings

In some cases, setting more than one configuration can impact the language in which an app is displayed.  For SharePoint-based apps (SharePoint sites, Groups, OneDrive for Business), the language is controlled by two settings - Office 365 and Delve.  In the case, where both are set, these apps will take the Delve language configuration.  When not set, the apps will revert to the Office 365 language.  So, in my case, the Site Collections were showing up in English (operating system language), while the sub sites were showing up in German (Delve language).  Once I enabled the alternate languages in the site collections, then those sites also appeared in German.

Delayed Settings

Some settings don't take effect immediately.  In my test, it took as long as 15-20 minutes in some cases to see these changes.  So, when you're making changes and want to see if it works, give it some time. 

The order in which you set the language also matters.  In my testing, when setting the Office 365 language after the Delve language, I noticed that the Delve language is overwritten by the Office 365 language.

Conflicting Settings

In some apps, I noticed that different language configurations were impacting the apps at once.  In the case of Sway, the browser setting was taking over the general UI, while some of the default content was appearing using the Office 365 language.

Tracking your Agile project progress using Azure DevOps and Flow - The Sprint Backlog

This is the second post that discussed how information tracked in Azure DevOps, such as the sprint burndown chart and current sprint backlog can be exported to SharePoint using Flow.  This scenario applies to times when you want to share such information with clients without giving them direct access to Azure DevOps.
If you're new to Agile project management, I recommend you have a look at a book I recently published on this topic called Agile Office 365.  Besides providing you with an overview of Agile project management using the Scrum methodology, it provides you with lots of information on how to manage your Office 365 projects using these methods. 

To learn about ways to generate a sprint burndown chart, check out Part 1:  The Sprint Burndown Chart of this two-part series.

The Sprint Backlog

A product backlog is a tabular list that is used to capture the work that the team will be working on.  Throughout the life cycle of the project, the backlog gets re-prioritized over and over to ensure that the team's focus is on the scope that is most important to the business.  In Azure DevOps, the product backlog is typically broken down into 4 levels:

  • Epic
  • Feature
  • User Story / Bug
  • Task

During sprint planning, the top items from the product backlog are further broken down and scoped out to form the next sprint backlog.

Microsoft Flow includes 13 actions that allow you to query and update the items.  You can use those existing actions and leverage the Azure DevOps Analytics extension to make your life a little easier.  

The Ingredients

To build out the solution, I used 3 technologies from the Office 365/Azure stack

Azure DevOps

For those new to Azure DevOps, I recommend reading Introducing Azure DevOps.  One key assumption in my process was that all the tasks within a User Story or Bug would be completed within a single sprint.  During my sprint planning meeting, the User Stories/Bugs would be assigned to a specific sprint.

In order to build the sprint backlog with the User Stories and Bugs assigned to the current sprint, I created a custom query.  From those work items, I was able to retrieve all the tasks underneath them well as the parent Feature and Epic they belonged to.  To get the results, I had to compose the following query:

(Work Item Type = Bug) OR (Work Item Type = User Story) AND (Iteration Path = @CurrentIteration)

To build the proper hierarchy and report on progress, I needed to pull the ID, Work Item Type, Title, and State in my query. 

This query, which I called Current Sprint Activities is a key component to my Flow that I'm discussing further down in this article.

SharePoint List

The goal of the SharePoint list was to reconstruct the hierarchical nature of the epic/feature/user story or bug/task relationship.  To do so, I leveraged a custom SharePoint list with the following fields:

  • Title - to display the User Story or Bug name
  • Feature - to display and group the User Stories and Bugs by Feature
  • Epic - to display and group the Features by Epic
  • Status - to  display the current status of the item


Flow is used to populate the SharePoint list with the sprint backlog items.  The flow is broken down into 4 steps:
  1. Delete all existing list items
  2. Get the information from Azure DevOps
  3. Update the SharePoint list
In my case, step 2 was done by a separate Flow as Azure DevOps was on a different tenant.

Get the information from Azure DevOps

To make it easy to pass back the Azure DevOps information to the calling Flow, I was generating a JSON object that contained the information using the following structure:

  { "User Story Title" : "US1",
    "User Story State" : "S1",
    "Feature" : "F1",
    "Epic" : "E1"
  { "User Story Title" : "US2",
    "User Story State" : "S2",
    "Feature" : "F2",
    "Epic" : "E2"


  { "User Story Title" : "USn",
    "User Story State" : "Sn",
    "Feature" : "Fn",
    "Epic" : "En"

The first step involved calling the query to get all the User Stories and Bugs in the current sprint.  

Next, I leveraged the Azure DevOps Analytics extension to make it easier to get the ID, title, and work item type of the parent item (Feature or Epic).  I could have used the Send an HTTP request to Azure DevOps action to get this information ,but would have required a bit more manipulation of the results to parse out the values.

By looping through the items from the first, I was able to compose the JSON object discussed earlier.

As a safeguard, I'm using replace() to escape any double quotes and avoid invalid JSON.  For example, for the title, I used replace(items('Apply_to_each')?['System.Title'],'"','\"')

As well, I used coalesce() in case the Feature or Epic were not set for a User Story or Bug.

Update the SharePoint list

Now that I have the Epic, Feature, User Story or Bug names and Status for each item, I can create new list items. In this case, I have chosen to use other Status terms (hence the if-expression)

To get the hierarchical view, I created a list view that is grouped by Epic and Feature.

The flow was originally set up to update nightly.  However, it could also be set up to run as soon as any changes are made to the backlog.


Using this method, I was able to share the current sprint backlog with my clients without giving them direct access to Azure DevOps.  This approach can be used to share other information from DevOps without giving any access beyond the project team.

Tracking your Agile project progress using Azure DevOps and Flow - The Sprint Burndown Chart

Microsoft Azure DevOps is a great tool that allows you to manage your Agile projects.  It allows you to easily track most of the information a scrum master or product owner are currently interested in, such as your product backlog, sprint backlog, burn down chart, and more.

If you're new to Agile project management, I recommend you have a look at a book I recently published on this topic called Agile Office 365.  Besides providing you with an overview of Agile project management using the Scrum methodology, it provides you with lots of information on how to manage your Office 365 projects using these methods. 

Some of the information tracked in Azure DevOps, such as the sprint burndown chart and current sprint backlog are often of interest to the clients to know how the sprint work is progressing.  Though you can extend Azure DevOps access to youir clients, this is not always desirable.  In this 2-part blog, I will discuss how I was able to bring key information from AzureDevOps into a SharePoint dashboard.

The Sprint Burndown Chart

Sprint Burndown charts are used to track the remaining work within the given sprint.  It typically looks like a downward trending graph with some ideal line.

The way the graph is generated is by summing the remaining work each day(y-axis) and plotting it against the timeline (x-axis).  Azure DevOps provides you with the ability to query various pieces of information, but there doesn't seem to be a simple burndown chart export.  So, here's how I was able to  achieve it.

The Ingredients

To build out the solution, I used 4 technologies from the Office 365/Azure stack
  • Azure DevOps
  • SharePoint list
  • Flow
  • PowerApps

Azure DevOps

This is an obvious one, as I'm basing my premise of this article that the project is being managed via Azure DevOps.  I am not going to get into the details of how to use Azure DevOps, as there are some great references already out there, such as Introducing Azure DevOps.

One thing I do want to touch on is a custom query I had to create for the solution.  Each epic, feature, user story, task, and bug in Azure DevOps contains a lot of data associated with it and you can create very complex queries to get a report of what you need.  In my case, all I cared about is a list of Tasks in my current sprint and the Remaining Work for each task.  When you build the query, there are two clauses I needed to include:

(WorkType = Task) AND (Iternation Path = @CurrentIternation)

Setting up this query provided me with a list of all the tasks within the current sprint.  The only column I needed to capture was the Remaining Work.

This query, which I called Hours Remaining is a key component to my Flow that I'm discussing further down in this article.

SharePoint List

The sprint burndown chart data is essentially tabular in nature.  You can store it in many ways - Excel, SQL table, SharePoint list, etc.  As my ultimate goal was to provide users with the sprint burndown chart on a SharePoint project site, it made sense to capture the information in a SharePoint list on that site.  The only columns I used were:

  • Title - to display the day number (e.g. Day 1, Day 2, ...)
  • Hours - to capture the Remaining Work for that day
  • Date - to capture the date of the remaining work


This is where all the magic happens.  The Flow is responsible to get information from the Azure DevOps query and update the SharePoint list accordingly.  In order to do that, the Flow is broken down into three parts:
  1. Determine if it's the start of the sprint
  2. Get the information from Azure DevOps
  3. Update the SharePoint list
In my case, step 2 was done by a separate Flow as Azure DevOps was on a different tenant.

Determine if it's the start of a new sprint

To know the start of a new sprint, I needed to establish the start date of the of the project and duration of each sprint (3 weeks, 21 calendar days).  With that information, I used a Compose function that would calculate the remainder of dividing the number of days since the project began to today.

The expression used determines the time in ticks, which allowed me to compare the current time to the start of the project.


ticks(variables('Project Start'))),864000000000),21)

If the remainder was 0, then it meant that the day is a multiple of 21, which meant that it was the start of a new sprint.  In that case, I deleted all my SharePoint list items and created new ones with the correct date applied for each sprint day.  

The Date field was calculated by adding 1 day each time in the Do-until loop. using the following expression:

formatdatetime(adddays(utcnow(),variables('Counter')),'MMM dd')

With the SharePoint list refreshed, it was now ready to receive the daily Remaining Work.

Get the information from Azure DevOps

As mentioned above, in my particular case, the Azure DevOps instance was managed by my company, while the SharePoint dashboard for the project site was on the client's tenant.  So I needed to do some cross-tenant work here.  To get the Remaining Work from my Azure DevOps tenant, I built a separate Flow with an HTTP end point that called my Azure DevOps custom query called Hours Remaining (discussed earlier in this article).

Once I got the remaining work, I calculated the total amount of hours remaining, and returned the total value to my calling function.

Update the SharePoint list

Now that I got the Remaining Work back, I was able to update the SharePoint list for the specific day.


The last step in my solution was to create a PowerApp to display the burndown chart for me.  Although somewhat limited in functionality today, I was able to use the charting functionality provided with PowerApps to give me the desired result.

All I had to do was to add a Line Chart control to the page, connect the SharePoint list as a data source, and map the Title and Remaining Work fields to the chart.

Putting it all together

Now with the PowerApp in hand, I was able to add a PowerApp web part to the SharePoint page to provide my clients with the information they required.  As a bonus, users were able to view the burn down chart directly on their mobile app if they desired.

Employee Check-in Simplified using Power Automate

Company employees who move between offices are often faced with a tedious process of having to check-in at the security desk to get a tempor...