Adatis BI Blogs

Why Delegation has a role even in PowerApps: Delegation Explained

In the first blog of this series: we looked at some tips to keep in mind when doing application development with particular focus on PowerApps. This blog will focus on an important point, more specific to PowerApps, which is worth keeping in mind during the development stage of a PowerApps application.What is Delegation for?Delegation permits the data processing to be done on the datasource’s end, and get only the required data back to the application, in order to avoid shifting all the data in the application itself and process it within. However, some of the functions in PowerApps do not qualify for delegation. Delegation is only supported for certain tabular datasources only – including SQL. If the application being developed is foreseen to work with small datasets that do not exceed 2000 rows (now or in the future), this will not be a concern. In these instances, any datasource or function can be utilized without delegation risks, as if the function cannot be delegated there will still be the ability of the data being processed locally within the application.Difference between a delegatable and non-delegatableIn short, what will be the implications if a function cannot be delegated? The data brought from the datasource will be much less (maximum 2000 rows) than what actually exist in the database, hence producing incomplete results (especially for lists or galleries) or inaccurate results (especially for calculations). Let’s go through an example of this, which architecturally might not be a feasible option to be implemented in a realistic application, but will help us understand better this concept. Let’s say a developer is doing a Form in a PowerApps that will take the details of the end user and submit it to a SQL database. In order to decrease chances of typos and allow better analysis on the gathered data, most of the dropdowns or lists within the Form are being loaded from another SQL database containing master data. And one of these lists is to be populated from the City table, allowing the user to choose from a pre-defined list of cities. The city table in the master database has thousands of records. Without going into the argument of whether this is a good approach or not, let’s say the developer populates a collection first directly from the master data table to be able to hook the list to it with improved performance. The SELECT query passed to the datasource as part of the Collect() function will include a TOP 2000 (beyond the developer’s control), and will only get back 2000 records of the full table. Likewise if a filter was being done or search directly on the database, it will only be considering the initial 2000 records out of the entire table in the datasource. Data row limit for non-delegatable functionsIt is important to note that by default the data row limit for non-delegatable queries is by default 500 (on creation of a new application). However, this can be increased to a maximum of 2000 (at the point of writing this blog) from the App Settings:Delegation WarningsWhilst developing the application, you might encounter warnings that will highlight that that particular action or behaviour might not work properly due to delegation. And this applies were that command will turn into a non-delegatable function. It is normally underlined with a light blue line, similar to the below. Here we are trying to get a dynamic list of all the products that have been inserted within this month and year:The same will happen if we try to get the products inserted in the last 7 days:The relevant control will also have a warning triangle displayed in Edit mode:Proceeding with publishing this application will mean that when the data row limit is exceeded in the source, not all the dataset will be considered for this command (and hence the result will not be exact).Possible workarounds?In certain cases like the above delegation warnings, a different approach for the same command can do the trick. So if upon starting the action we are getting the Start Date once and putting that in a variable, whilst filtering against the range of that and today, then no delegation warnings will be given:A similar solution would be to refer to a used Label on the canvas that is calling the DateAdd(Today(),-7) and referring to it in the Filter instead of the variable. One other option to go around delegation is loading a collection at the start of the application from a large table (as long as it is controllably large, and not millions of rows), and looping through it a number of times (the maximum key in the datasource divided by the default data row limit of 500) to populate the collection with the entire dataset. This can be done either directly in PowerApps or even using a SQL function similar to the following. In this table-valued function we are aiming to output a table with 3 columns. The first column will be the PointerID holding the list of times we are going to re-loop over a database to get up to the maximum row. For each PointerID we will be setting the RangeTo and Range From, when considering the data row limit in PowerApps (which is accepted as a parameter).Following is the SQL code for the function:CREATE FUNCTION [dbo].[GenerateReRunTable] (@Limit INT)RETURNS @ReRunTable TABLE (PointerID INT,RangeFrom INT,RangeTo INT)ASBEGIN--Replace [dbo].[LargeTable] with your table name and the [Id] column with your key--Getting the minimum and maximum from the table to set the entire rangeDECLARE @RangeFrom INT = (SELECT MIN(L.Id)FROM [dbo].[LargeTable] L)DECLARE @RangeTo INT = (SELECT MAX(L.Id)FROM [dbo].[LargeTable] L);--Calculating the number of reruns that need to be done by dividing the range of records with the data row limit in PowerAppsWITH MaxReRunsAS (SELECT ((@RangeTo - @RangeFrom) / @Limit) + 1 AS [MaxReRuns]),ReRunsAS (--Generating the runs including the unique PointerID for each and their start and end range to then be picked by PowerApps for filteringSELECT 1 AS PointerID,@RangeFrom AS [RangeFrom],CASEWHEN @Limit < @RangeFromTHEN @RangeFromELSE @LimitEND AS [RangeTo]UNION ALLSELECT PointerID + 1,[RangeFrom] + @Limit,[RangeTo] + @LimitFROM ReRunsWHERE PointerID + 1 <= (SELECT [MaxReRuns]FROM MaxReRuns))INSERT INTO @ReRunTableSELECT *FROM ReRunsOPTION (MAXRECURSION 10000)RETURNENDGOA SQL View can then be created to get the results from the function. That way whenever we are loading the view, we will be getting the latest result based on the updated data, like for example:CREATE VIEW [dbo].[ReRunsView]ASSELECT * FROM [dbo].[GenerateReRunTable] (500)In this case, the [dbo].[LargeTable] had about 5203 rows. Hence the view would show the following output:In PowerApps, we are then making use of this code (either in the OnVisible() section of the screen or in the OnSelect() of a button):ClearCollect(ReRunsColl,'[dbo].[ReRunsView]');ForAll(ReRunsColl, Collect (ProductListColl, Filter ('[dbo].[Ori Product List]', ProductID >= RangeFrom && ProductID <= RangeTo)))So, for each line in the SQL view (that has been loaded to the collection), we will be posting data to a PowerApps collection named ProductListColl all the contents from the SQL table named [dbo].[Ori Product List] in batches filtering on the specific range for that run (so that we bypass the delegation row limit, which will else disregard the rows above the row limit). Else, a normal Collect(ProductListColl, ‘[dbo].[Ori Product List]’) outside the loop would have only populated the collection with the maximum data row limit allowance of 500 – providing an incomplete dataset. This fully populated collection will also enable us to refer to the ProductListColl locally (as a replica of the entire [dbo].[Ori Product List] SQL table), increasing also the performance of data retrieval from this particular dataset. We need to keep in mind though that eventually in PowerApps, we will still are using a ForAll() loop to read and hence doing multiple calls to the database when dealing with large datasets, increasing the chances of encountering issues which I will be explaining in later releases of this blog series. This will also have an effect on the performance (till the loop is being done and collection populated).I also intend to finish a more practical example related to this to highlight better how delegation works in PowerApps in the upcoming blog post within this series – so please stay tuned for next release! In the meantime feel free to exchange any ideas or comments on this topic! 

Considering agility and flexibility when developing with PowerApps

Even if PowerApps truly is a low-development option for building applications, it would still be important to approach application development with best practices that are normally applied in other development technologies. For anyone who has developed applications in the past through any programming language, such tips might be already known or have now even became obvious. However, in this blog series, I will be collating some guidelines which will be useful when developing a PowerApps (and probably any other) application.Development methodologyEven if PowerApps may provide a more controlled way of designing and developing an application, and an easy approach of adding more features as you get along – care should be taken when developing extensive applications which are composed of many screens, features and functions (in the same way as any other application development technology). It is hard to generalize and suggest a one size fits all approach for all PowerApps use cases. However, I personally found it helpful to start with having a draft UX design of all screens setup and run by the end-users/ stakeholders before going through the application of the logic itself. Although PowerApps is a low-development technology, some level of coding and logic is still required. And in cases where this logic needs to do more than just a Submit(Form) or a simple Patch() command, thought would need to be applied in almost the same way as developing an application with other technologies. For instance, there are cases where variables need to be reset through the OnVisible() attribute of a screen (so that when a screen loads, the variables are fresh to be re-used). There are cases where a Refresh() needs to be done – for example right after an update to a SQL database, if the latest added record needs to be shown in a Gallery or other lists. And such commands need to be implemented based on the design and user journey that exists in an application. Being too agile in developing a PowerApps application might increase the risk of technical debt. One additional step in the middle of the user journey, requested when the basis of the application and its logic have been applied, might mean that what has been properly developed and worked, is now to obsolete from a logic perspective. Hence rework needs to be applied, increasing risks of mistakes, bugs or flaws. Likewise, what has been tested and approved before, now needs to be retested from a functional, UX state and unit testing point of view. This means that one slight addition of a feature (maybe another update or just a change of a background colour of a label/ button depending on a validation/ boolean check), could trigger far more indirect effort to ensure the application still satisfies scope and quality.HardcodingLike any type of coding, hardcoding when building a PowerApps application might not be the best practice and hence is ideally avoided. Through hardcoding, assumptions are being done during development stage that the application coding will remain static and any values being used or referred to within it are not going to change. For example, a filter is done on a hardcoded value of an organization name directly within the code, like Filter(OrganizationTable, OrganizationName = “CurrentName”). A change in the name will require a revision of the code, whereas if the check was being done using the record’s key in the database or a populated variable (rather then the value itself), the change of value would only be required within the database.In a similar manner it is important to adapt this logic approach to any areas where potential additions or changes in the future might occur. So for instance, let’s say an If condition is going to be done within the program to only enable a button when a value is positive. And let’s assume the positive and negative value are being picked from a junk dimension or any other table. It might be that at the time of developing the application, there are only two values present: a positive and a negative value – i.e. “Yes” and “No”. If the button needs to be only visible when the value is positive, there are two ways of writing the If condition:· If(Variable <> “No”, DisplayMode.Edit, DisplayMode.Disabled)· If(Variable = “Yes”, DisplayMode.Edit, DisplayMode.Disabled)At the point of development both commands are going to return the same result. However in the event that in the future a neutral value is added – let’s say “Maybe” – and some conditional checks might hence include any of “Yes”, “Maybe” and “No” – the results of the conditional check might be skewed if we had applied the first command. This is because the button will be enabled both when the value is “Yes” and also when its “Maybe”, whereas the button was required to only be enabled on “Yes”. Now when the same developer might be maintaining this application and the database, and the number of screens is minimal or with limited coding logic, one might argue that the command can easily be changed within the application coding and a new version of the application is published. However as best practice, it is always advisable to develop the application with the scope of doing minimal changes to the coding, as there might be derived effects of a simple change on other functionality in addition to the potential need of having the entire application re-tested (especially if we are dealing with a sizeable application).Commenting codingAs with any other development tool and language, it is a good practice to comment the coding applied in the different screens. With PowerApps application (like other technologies) having higher chances of being developed by one person/s and maintained (now or in the future) by other person/s, troubleshooting issues or even judging the effect of a future change will become harder without commented code. PowerApps comments start with // and adding simple comments like “//Refreshing datasource to get latest data in Gallery GalleryName post-patch to its datasource DataSourceName” might be enough for a different developer to realise why that command should be or not be changed (and the tester to know what to focus on if that is changed after all).Testing, testing, testingA developed application is going to be used by different users, and in cases where the application is not a two-screen application, different user journeys might exist. Also using the logic path of the application might be different depending on the user behaviour, and especially in cases where If…Else conditions are being used. In such scenarios, it is even more important to ensure the application has been tested over and over, and preferably by different persons (to ensure different perspectives and behaviours) hence increasing the chances of covering as much user journeys and logic paths as possible. As with any other technology, the use of testing logs is suggested to keep track of the flaws, bugs or improvements noted and if possible the pattern of reproducing that error is also noted.In cases where different navigation options exist (like back and forward from each page in addition to a normal menu), one should not underestimate the importance of re-testing following successful runs, trying different combinations or patterns, especially if variables are being passed from one screen to the other.In a following blog within this series, to be released soon we will continue to cover more points related to PowerApps and application development – also highlighting PowerApps specific pitfalls worth avoiding! As always feel free to reach out with any feedback or clarification required on any of the above points either through our blog itself or on Twitter: @bonello_jason

Getting user list from Office 365 in PowerApps

ContextIn the previous blog, we have gone through an overview of where PowerApps could be used and also some ideas on APIs that can be connected to it: this blog, we are going to go in some detail on the connection of PowerApps to Office 365 – and some examples of how this integration could be beneficial in application development through PowerApps.Office 365 and PowerAppsOne way though which PowerApps can be accessed, is through an Office 365 subscription. Once logged in you will see PowerApps available under one of the apps with this platform.Yet the relationship between Office 365 and PowerApps does not stop there. When building an application in PowerApps – which is going to be accessed in an Office 365 platform – there are various commands that may be used to add features to the developed application.For instance, presenting a list of users in a dropdown dynamically based on the Office 365 directory would be easy and adding “Office 365 Users” from within the data sources list will enable this.Once the connection has been added, two elements and a screen in PowerApps should be enough to provide the option to choose one profile from Office 365 directory.A ComboBox could be added from the Controls, and in this case we added a Label (which although it does not affect the functionality itself, gives a description). A ComboBox is like a normal Dropdown (which is classified as a separate control in PowerApps) but which amongst other slight differences, allow the selection of more than one item from the item list of the dropdown. It is worth saying that the allow multiple selection option can still be switched off. But I still prefer using this over a Dropdown because it allows the search option (which again can be enabled or disabled). This will apply an interactive filter to narrow down the list in the dropdown items.“User:” has been typed in the Text of Label1 and placed the ComboBox just beneath it on the screen. What the user inputs as a search text when using the application within the ComboBox can be called within the application using the SearchText function. In the Items of ComboBox1, which will determine what the user is to be presented with when the dropdown is clicked on, the connector is called through the Office365Users command. The SearchUser operation is then added, and as a searchTerm (which can be used as part of the SearchUser operation to filter the list), the ComboBox’s SearchText is to be used. The complete and proper syntax can be seen in the next screenshot.It is also important to specify which Office 365 fields are going to be used in the list and for the search – in this example it was the DisplayName.That is basically enough to present the PowerApps user a dropdown to filter and select a profile from. When running the application, the PowerApps user will start typing the display name that s/he intends to find, and the list is narrowed down. The user can then select the appropriate display name.What is also useful, is the ability to add other Labels, in order to get more information from the selected Office 365 profile. And this can be done with a simple syntax like the following:This way every time the selected profile is changed, the value of Label2, will change automatically depending on the selected value.Instead of MailNickname, there will be a whole list of attributes that can be extracted from Office 365 profile like City, Country, Id, Mail, contact numbers etc.Likewise, like the SearchUser() operation, there are many other operations that can be used in conjunction with Office365Users connector command. One other notable operation is Office365Users.UserProfileV2() which enables retrieval of data by passing the Profile Id or User Principal Name in order to get a whole list of information including skills, past projects, responsibilities, schools, interests and such fields that are available in Office 365 user profile:This is an example of the command that can be used for this:First(Office365Users.UserProfileV2(ProfileId.Text).skills).ValueProfileId in the above, is a label that is using a command to get the Id – which we will explain in the next section.User identificationHandling user logins and presenting different screens to different users within the same application is also easy.In order to, identify a user that has been logged in and display her/his details, another 3 elements within a screen should also be enough.One of the elements could be an image, added through the Media tab. And the User() function could be used to get back the image of the user (as updated in her/his profile).Two Labels can then be added and placed under the image, one displaying the name and the other the email with this syntax for each label (in Text property):Once the PowerApps user logs in, the details will show up on this screen. And similarly, the email could be obtained and used to filter out/ give access to certain controls or screens within the application.It is also worth noting that other functions like Office365Users.MyProfileV2() can be used to get a whole list of other details for the logged user – if the user has logged in through the Office 365 account.Getting back to the ProfileId label mentioned in the end of the previous section, the following command is being used to get the Id:Office365Users.MyProfileV2().idIt is important to keep in mind that even if there are various ways of retrieving such information, ensuring the use of a command that guarantees the retrieval of a record using a unique key is imperative. For instance, this command also returns an Id:First(Office365Users.SearchUser({searchTerm:User().FullName})).IdHowever, there can be more than one user in an active directory with the same full name. In this command we are getting the first profile and returning its Id – which is not always guaranteeing us the proper profile Id, especially if another user exists with the same name as the logged in user Wrap-upThis explains how easy it will be to develop and create such a capability in PowerApps solution. As pinpointed already, Office 365 and PowerApps offer other features that can be maximized through this connection and integration. And in a similar manner PowerApps can produce other positive results when connected and integrated with other relevant data sources and utilized as a user interface through application development!

Why PowerApps?

Having had the opportunity to work with the development and delivery of PowerApps applications on different projects, I feel that this technology provides a great modern solution to fill in business technology gaps, be a companion to software-as-a-service models and serve as an integrated frontend to various enterprise architectures.Uses of PowerAppsThrough PowerApps application, a user or business could now be presented with a simple and user-friendly, user interface to input or maintain data in different modes and for various reasons. Such applications can be designed to be used on mobile, tablet or desktops.PowerApps is another tool in the powerful Microsoft Power Platform. Most of us already had the chance to experience the benefits of Power BI within the reporting, dashboard and data visualization spectrum. When building reports through Power BI, it is normally essential to use Power Query to cleanse, standardize and prepare the data to a reportable state prior to modelling it and consequently start the development of the report. The effort required in this stage varies depending on the state and consistency of the dataset or database that is to be used to build the reports. And another benefit of PowerApps could be highly experienced at this stage.PowerApps could help in ensuring that data is kept in a consistent, organized and meaningful way, as opposed to leaving data entry validation at the stake of the user and applying endless data cleansing and preparation phases after its commitment. This will hence be also beneficial to any other data architecture like Data Warehouses. To highlight another benefit of PowerApps, building an application with this technology takes much less time then if coding such an application through managed coding languages. Likewise, I would say it also replaces the need to develop through VBA in Excel – by presenting a more scalable, modern and intuitive alternative.In this blog I am going to particularly focus on connections to data sources within PowerApps from a general perspective.Data sources in PowerAppsPowerApps allows the connection to multiple data sources and APIs. It can hence serve as a common handler of the data in different systems being used simultaneously within an organization.In order to include a data source in your application, the following few clicks are needed:1) Just go to View on the top ribbon2) Then click Data sources3) A column will show on the right, where the New Connection button can be used to add the required data source.4) And voila! There’s a whole list of connections to choose from, which range from other Microsoft products like Azure and Dynamics 365 to others like Google Calendar; social media favourites like Twitter, Instagram and Facebook; Marketing automation platforms like MailChimp; CRMs like Salesforce; Zendesk and so on.Once the login details for the connection are added, that data source could be used in the PowerApp solution in many ways as a data source, data processor or as a data destination.It is worth keeping in mind that some API connectors might only be available in Premium (which thus relies on the price tier/ under the plan that you are on). Such connectors are clearly identifiable with a gem icon beside them in the list. Some other connectors have a bulb icon beside them. This just shows that the feature is in experimental or preview. This serves as a warning to the developers when designing and developing their solutions, that such connectors are still subject to change and to be cautious when basing their system on them.Some connectors might also require some other details during development to activate their usage within the application, ranging from login details to Azure Keys etc. Yet once the application is published these connectors are treated as part of the application and any user granted access to the application will inherit the right to use these connectors as part of the application itself (without the need for her/him to have a relevant account for each and every connector). Upon loading the application the first time, the users will be prompted by a one-time message to have access to the connectors within:Once “Allow” is clicked, the users will be able to use as part of the application itself (with no other access outside the application being affected).Azure services with PowerAppsWhen acting as a frontend to Azure services, PowerApps can provide a cloud-hosted scalable solution facilitating the operation which might have otherwise required technical expertise.Just an example of this, Cognitive Services within Azure offers different AI solution that can be put in practice on a business level. Face API is one of these services, that can be used as a face verification and detection whilst integrating emotion recognition. Thus, by adding Face API as a data source in PowerApps (along with some other technologies like Microsoft Flow) one can easily provide a front end to take a person’s face image, have buttons included that trigger the analysis of those images through Face API and Microsoft Flow. Additionally, the results can be retained immediately and displayed in the same PowerApps solution. Azure Storage could also be added as part of the flow processes in order to retain a copy of the analyzed pictures – if required.This applies for the utilization of other Azure service in conjunction with PowerApps.The fact that PowerApps allows ease of input through Camera, Barcode Scanner and Microphone, it makes this technology versatile in data inputting methods and user interface adaptations. It hence also allows the opportunity to utilize the wide breadth of Azure Services that correspond with these data input methods.To sum up, this gives some ideas of PowerApps usage and an example of some applications that can be developed through PowerApps. It is also worth noting that PowerApps allows also the connection to custom APIs that have been produced through tailor-made development. Hence in these cases it will also serve as a potential front-end especially in cases where expertise in technical languages used in UI is lacking.

Iconography in Design

In this blog I will go about discussing the importance of design, in particular looking at how icons can help add the finishing touches to a piece of front end development. Whether you’re building a Power BI report, SSRS report, PowerApp, or doing any kind of front end visualisation and design, you’ll know that 50% of the battle is making whatever you’re building jump out of the page. You could have built the most useful report that a user could wish for, but unless it looks good, you’re not going to get any plaudits. Essentially, good design goes a long way to making a success on a piece of development. This is even more important if you’re building the underlying architecture / ETL, and for all our good practice and thoughtfulness in this area as developers, will rarely impress or impact on an end user. Its important to get inspiration for design from somewhere, so before going about designing or even building something, take a look on Google, do some research and try to look for similar things to what you’re doing and see what you think looks good, or not so good. You should build an idea up of how you want to design you’re landing page, report or report headers, etc. within the tool. If you’re in Power BI, take a look at the partner showcase for example. There’s some really good examples to give you ideas – like this one! Microsoft apps such as Power BI or PowerApps go a long way in helping us build the best when it comes to data visualisation, but unfortunately they sometimes rely on developers to go outside the box to finish things off.   Icon Finder Recently I’ve started to use a site called which has a large pool of useful icons you can use for building out certain corners of apps that the standard MS tooling will not support. The icons are mostly $2 each, but you can get a subscription for £20/month or $10 with a discount code (which you should cancel as soon as you register). Please, don’t jump in if you think you need to use this as a resource. Alternatively, save the icon using Chrome and use it as a placeholder until you are happy to push to production. For non-subscribed users, the icon will always come on a background of faint grey lines. This isn’t too bad as they don’t completely ruin the look and feel of the icon in the development and are good for a placeholder for demos, etc. To get started, just type in your keyword for the type of icon you’re looking for, and then its just a case of wading through the results to find the icon that fits the look and feel your inspired to build against. Sometimes, you’ll get another bit of inspiration off the back of this which you can use as another key word to find even more icons. The site also comes with a very handy icon editor tool, essential Paint Shop Pro on the web. There’s lots of these sites out there but its useful its all integrated into one place at no extra cost. It will load the SVG icon into it automatically if subscribed which then allows you to edit colours or shapes etc. In my instance, I found a nice % complete icon set which would look good on a white background. Unfortunately, I wanted it on a blue header bar, so needed to change it up slightly to fit the look and feel. No problem, took less than a minute to modify and download. Its also worth mentioning that the site does a good job at helping you find a pool of icons which will fit together nicely using the same look and feel, showing you icons from the same icon set. In one instance, I replaced an icon I was looking for to be from the same pool even though the icon wasn’t exactly what I was looking for – because overall it just felt like it fitted together nicer with the other icons on the screen.   Design in Practice As mentioned above, doing some research before you build can really help you create a much better finish. For inspiration for a recent PowerApps design I did a quick search for landing pages on Google, and found a few I liked the look of (below). As long as the general elements you are working to are similar, it really doesn’t matter where the inspiration comes from. In these cases, they were in the form of mobile apps. From these images, I was able to identify the key components which made me bookmark them: I wanted some kind of non-offensive background, possibly semi-transparent, or with overlay. I wanted a title that stands out the page, so white on grey or similar. I wanted a small section for a blurb for the PowerApp. I wanted 2 buttons, and the buttons to stand out. I wanted logos in the top corners. I wanted a nice look and feel for the colour palette. From this, I then produced the following landing page. I found the background on which turned out to be quite a nice resource for some generic business style artwork, and then added a blurring filter across the top. This still interferes slightly with the buttons / title so I’m not completely happy but satisfied enough that it achieves the look and feel I was looking for. For the title, the range of fonts supplied with PowerApps is rather limited so I could go externally for this too but was happy enough for the time being. The layout also leaves room to shrink the title and add a small blurb if need be. The buttons are made up of a number of icons and fit with the theme for the app. As mentioned above, I also added % complete icons to each page so users were able to understand how far they were along the scoring pages within the app. PowerApps provides sufficient icons for the back/refresh buttons that fit in with the white on blue theme, so I didn’t have to go externally for these. These were placed on the page header next to the logo.     Power BI While this most recent bit of design was focused on PowerApps, I also add small bits into PowerBI during report design. For instance, rather than just have a generic button that can push you to a “details” page which has a table for the row by row breakdown of some aggregated data - I looked for an icon, edited the colour palette slightly and added this to the report. With recent Power BI functionality, I can make the image act as a button and redirect the user to another page. I’ve also used icons in dashboard design where a single visual didn’t really represent the content of the report to drill into. This can also be a good way to go about adding a bit of flavour to a dashboard to mix things up. In my case, it also meant the drill down into the report level was less ambiguous, by asking a question as the title if that’s what the user wants to do. Design is always subjective of course, but its great to use other resources at your disposal to go about building out apps. Depending upon the current estate in which you develop, it also helps them stand out a bit more and add a unique context to the reports/apps within the project. Hopefully this blog has given you a few ideas for your next project!

Embed PowerApps into Power BI Desktop

Microsoft’s January 2018 Power BI Desktop update (blog found here) contains quite a few small visualisation features, but the one that stood out most to me is the ability to import PowerApps as a Custom Visual. My last two blogs (Part 1 and Part 2) demonstrated how to embed a PowerApp into a Power BI Dashboard in the service, but it wasn’t possible to achieve this in Power BI Desktop or a Report.  How things have changed within a month! This article illustrates how quick and easy it now is to plug an existing PowerApp into Power BI Desktop.      PowerApps Custom Visual Like all popular Custom Visuals, you can either Import from file (stored on a local machine) or Import from store. The Microsoft endorsed Custom Visuals are found in the store, which is why I would generally advise importing visuals from there.  Search for “PowerApps” and it will appear as the top result. Click ‘Add’ and the PowerApps Custom Visual is available. NOTE:   The PowerApps Custom Visual is currently in Preview, meaning its reliability cannot be 100% guaranteed.  Always use Preview tools/features/visuals with caution. PowerApp Connection I previously created an ‘SvT Adfjusted’ PowerApp, which will be used for this example.   Firstly, the visual needs to have ‘data’.  It can be an attribute or measure, but doesn’t matter when connecting to an existing PowerApp.  If you haven’t logged into the PowerApps portal, you may be promoted to do so with Power BI Desktop.  Once logged in, you will see the below screen:  If you see the ‘not supported’ error message above, do not worry – this is red herring.  Click ‘OK’ and then navigate to the appropriate Environment within the nested PowerApps portal.  ‘Adjust Target App’ resides in the ‘Callums Blog’ Environment. Click ‘Choose App’, select the PowerApp and click ‘Add’. That’s it, the PowerApp is now embedded and ready to use in Power BI Desktop. It is also possible to create a new PowerApp within Power BI Desktop, which is demonstrated in this video. The look, feel and general experience is the same as what you see in the PowerApps portal, meaning you do not even need to use a web browser to get started. PowerApps Refresh I wander how long Power BI Desktop takes to refresh when a change is made to the embedded PowerApp?  Let’s find out. Before: Principal A ‘Target’ value is changed from 850000 to 950000.   Unfortunately, nothing happens.  Power BI Desktop caches the data sourced from the Azure SQL Database, which is where the PowerApp data is stored.  The only ways to view the change is to click the Power BI ‘Refresh’ button or change the context of the visual interaction within a report.  What I mean by the latter is that you need to force the Tabular engine to write a new DAX query, which in turn, forces a query refresh.  Using the ‘Refresh’ button (below) will always be the simplest refresh method.   After: Published Report The report can now be published to the Power BI Service. The Service contains the same refresh issues as described in Power BI Desktop. Manually refreshing the web page (CTRL-F5) is currently the best approach.  It is worth noting there is between 15-30 seconds delay between a submitting a PowerApp change and viewing the new number in a visual.  This is expected, as there are a few processes (behind the scenes) that must happen first. Web Content vs. Custom Visual There are now two ways of adding a PowerApp into the Power BI Service – but which is better? Web Content -          Advantages o   Easy to dynamically add a ‘Web Content’ tile to a Dashboard. o   More flexibility around the link behavior e.g. open custom link or other report in a new tab. o   The PowerApp sits completely outside of Power BI and does not necessarily need to be seen in a report. -          Disadvantages o   PowerApp embed code is required and may not be available to all users. o   Extra layer of management and maintenance. Custom Visual -          Advantages o   No increase in the Power BI Desktop data model or file size.  The PowerApp is sourced from the cloud and acts as a Live Connection. o   One stop shop where both visuals and the PowerApp reside. o   Less places for an issue to occur.  Security can also be resolved, using Power BI Desktop as a test. o   PowerApp can be pinned as a visual - to an existing or new Dashboard.  This step is far more user friendly and easier to achieve with the Custom Visual. -          Disadvantages o   No ability to use custom links. Whilst Web Content does offer a bit more flexibility and interactivity, I would advise using the Custom Visual wherever possible.  Having one place where everything is managed, makes everyone’s lives far easier.  If you have a good business case for adopting Web Content, then of course, please do so.  References Find other recommended resources below. o   PowerApps Custom Visual Video - o   Power BI January Update - o   PowerApps - o   Flow - o   Common Data Service - Contact Me If you have any questions or want to share your experiences with the PowerApps Custom Visual, feel free to leave a comment below. Twitter:                @DataVizWhizz

Embed PowerApps into Power BI Dashboards – Part 1

Having read Matt How’s blog (found here) about PowerApps, not only did it get me interested in the technology, I also wondered how well (if at all possible) it could integrate with Power BI. Our friend Google soon told me that it was already possible to embed PowerApps into Power BI, released in the April update. However, apart from this blog by Ankit Saraf, there aren’t many professionals sharing their experiences. In addition, leveraging Direct Query mode in Power BI means we can simulate real time user input and reporting. To replicate my solution below, you will need an understanding of PowerApps, Azure SQL Database, Flow and the Common Data Service (CDS). The Further Reading section provides some good links to get you up to speed. I have broken the blog into 2 parts: -          Part 1: How Power BI visuals and PowerApps can be used together. -          Part 2: Benefits and Drawbacks of the tools/processes used. Solution I picked a typical use case that would link Power BI and PowerApps – Actual vs. Target. The Power App will be used for adjusting target values, whilst an Azure SQL Database will contain the original target and actual values. All data and Power App interaction will be embedded into a Power BI Dashboard. Create Sample Tables and Data in Azure SQL Database Create and populate two tables – dbo.SvT for static actual vs. target data and dbo.SvTAdjusted that will eventually contain the adjusted target data from the PowerApps form.             Note:     Azure SQL tables require a Primary Key column to communicate with Flow and consume CDS data. Create PowerApp Create an Environment within the PowerApps service, adding two new Connections:   1.       Connection to the CDS, using my company Microsoft account. This is where the adjusted budget values reside. 2.       Connection to the Azure SQL database, which will become the destination table to store the CDS Power App data.   The next step is to import the SQL Data from dbo.SvTAdjusted directly into a CDS PowerApp.     This automatically creates a user form containing the data. Here is where you can customise the PowerApp, such as making fields read only and configuring look and feel.     Publish the App and test and change the ‘Target’ values to test. Create Flow trigger Navigate to and login. Create a new flow, searching for ‘Common Data Service’ as the connector. Select the below and create the Flow.     Select the PowerApp CDS Entity (Adjusted Target) as source.     Add a new step (Add an Action) and search for ‘SQL Server’. Select SQL Server – Update Row as the destination and map to the dbo.SvTAdjusted table. The column data types between CDS and Azure SQL Database must match when being mapped. Save the Flow.       Create Power BI Report Create a Power BI Desktop report and connect to the Azure SQL Database. Set up the one to one relationship on ‘PrincipalID’, between the tables. Create some KPI’s and a table to compare dbo.SvT and dbo.SvTAdjusted metrics. In the below example, the ‘Adjusted Budget’ metric will change when we make changes in the CDS Power App. Embed Power App into Dashboard Publish Power BI Desktop report and pin as a live page. To embed the PowerApp into the Dashboard, add a Tile and select Web Content. The App ID can be found under Apps in the Power Apps web portal. Simply paste the App ID into [AppID].  <iframe width="98%" height="98%" src=",34,55,1)&appId=/providers/Microsoft.PowerApps/apps/AppID]   The PowerApp is added as a Dashboard tile. It is now possible to change the ‘Budget’ values.       Time to test everything works. Change the values for all three records and refresh the Power BI Dashboard. The values have changed almost instantly!     Further Reading Check out Part 2 of the blog, where I will be discussing the benefits and drawbacks I have found with using Power BI and PowerApps together. Find other recommended resources below. o   Matt How’s Blog - o   Embed PowerApps into Power BI - o   PowerApps - o   Flow - o   Common Data Service - Contact Me If you have any questions or want to share your experiences with PowerApps and Power BI, feel free to leave a comment below. All scripts and workbooks are available upon request.Twitter:                @DataVizWhizz

Embed PowerApps into Power BI Dashboards – Part 2

Part 2 of this blog focuses on my experiences with PowerApps, Flow and Power BI. Part 1 was more of a demo and ‘How to’ guide, but when I read an article online, I always find known limitations, challenges or workarounds as the most interesting takeaways. Without further ado, here are my findings.   A summary of both blogs below: -          Part 1: How Power BI visuals and PowerApps can be used together. -          Part 2: Benefits and Drawbacks of the tools/processes used. Benefits -          Easy to get started. Rolling out Power Apps, Flow and Azure databases into production of course needs careful thought, but for Proof of Concept’s, Flow (2,000 runs per month) and PowerApps (for Office 365 users) are free to use. Links to the price breakdowns are provided in the Further Reading section below. -          There are a range of Wizards, Templates and GUI’s. All the tools used offer great templates for moving or inputting data and the fact barely any code is needed, makes it simple for business users. Following a couple of YouTube tutorials on each technology will get people up to speed very quickly. -          Azure technologies provide seamless integration between Microsoft tools. Whilst there are some other well-known, reputable cloud service providers around, using one product is always going to produce a slicker solution. Having less configuration steps means less chance of human error. -          Customisable features of PowerApps give the ability to mask, validate and format the PowerApp screens. It also makes the user entry a more pleasant experience, as the forms are more intuitive. Limitations -          You can only embed PowerApps into a Dashboard – as a Tile. I am not sure if moving PowerApps into a Power BI Report is on the roadmap, but I would be surprised if it was never supported. -          Power BI Dashboards are cached and not entirely real time. You can change the cache settings to 15 minutes, but the best way to ensure your visuals contain the latest Power App data is to manually refresh your page in the browser. Reports do update automatically, which makes it even more frustrating. -          Common Data Service (CDS) is a preview Data Connector in Power BI. As a result, you need to either have your environment set as ‘America’ and/or been given the beta by Microsoft. If I had access to this connector, there would have been no need to have the Azure SQL Database or Flow trigger. Milinda Vitharana’s blog shows how to enable CDS Power BI Integration. -          If you wanted to use an on-premise database instead of an Azure database, an additional step is needed. A Data Gateway (link here) must be installed to move the Power App data back into the SQL database. Therefore, I would always recommend (where possible) using PaaS or other cloud services, as they talk to each other natively. -          The error handling within the PowerApps is still quite limited. If Flow fails when updating data between PowerApps and Azure SQL Database, nothing is captured within the form itself. An Admin would need to check the Flow job or set up email alerts for user’s peace of mind.     Conclusion The initial signs look promising for Power BI and PowerApps integration. I managed to create an Actual vs Target Proof of Concept in just a matter of hours, without any real coding. There are still quite a few drawbacks and hoops to jump through to bring everything into a Power BI Dashboard, but I can only see things getting easier from this point. There are other use cases for embedding a PowerApp into Power BI, such as monitoring live sales and re-ordering stock within a PowerApp or updating product descriptions that automatically updates the Dashboard attributes. Giving someone the ability to directly interact with a Dashboard and make instant business decisions is priceless in today’s fast paced world. Further Reading Find other recommended resources below. o   PowerApps Pricing - o   Flow Pricing - o   Milinda Vitharana’s blog - Contact Me If you have any questions or want to share your experiences with PowerApps and Power BI, feel free to leave a comment below. All scripts and workbooks are available upon request. Twitter:            @DataVizWhizz

How to Find Your Next Job with Power Apps and Flow

Both PowerApps and Flow exist within the Office 365 suite and bring enormous amounts of possibilities to mildly technical business users. No longer will Dan in IT who knows a bit of VBA be hassled to write a dodgy macro that puts some data in a database. Not only that, business users can now reach out to literally hundreds of other services that come connected straight out of the box! In this blog, I’m going to demonstrate a way we can use PowerApps to put a professional and mobile ready interface onto a Flow, allowing us to query an API and present the results back using Power BI.   Creating a PowerApp You can create a PowerApp in either the Web Portal or using PowerApps Studio ( I personally prefer to use Studio but both work the same, and actually all connections, Flows and custom APIs are managed through a web portal. If you have ever developed with Windows Forms then PowerApps will feel very comfortable. There isn’t a toolbox as such but you can easily drag and drop controls from the ribbon bar and all the properties live on the right-hand side. It also holds some similarities with Apples xCode in the sense that you can see all your Screens (Scenes in xCode) on the left. 1. Ribbon Bar: Here you can drag and drop a wide range of controls, galleries and media APIs onto the App design screen 2. Preview App: This button will run your App/debug. You can also use F5 3. Screen Viewer: Here you can see all the screens that make up your App 4. App Design Surface 5. Properties Window: Configure properties about the controls within your App   The Common Data Service Because we are looking at this from an Office 365 perspective we can make use of the Common Data Service, but we could also choose from any other relational data store including Oracle, MySql, SQL Server, SharePoint etc. As it says on the tin, the CDS is a generic, cloud hosted database that gives users the ability to create their own datastores and then share those throughout the organisation using AD. It also integrates very nicely with PowerApps and Flow meaning we can avoid any SQL DDL or Stored Procedures. Out of the box you get a range of standard tables that cover off a variety of business needs but you can also create custom entities that can tailor the CDS to your specific needs. Here’s an example of an entity I created in CDS to use as the main datastore for my App. 1. Ribbon Bar: New fields, Import/Export, Settings and Delete 2. Tab Bar: Fields and Keys. Preview Data within table 3. Custom Fields: Showing data types, Nullability and Cardinality 4. Standard Fields: Audit fields e.g. Created by / Created on   Developing a PowerApp One of the best features of PowerApps is that it is very smart with metadata, we simply need to point it at a table and PowerApps can use that to make decisions on how to construct your App in a way that suits the C.R.U.D. needs of your datastore. By creating the app from the custom CDS entity, PowerApps will know that you need a browse screen, a details screen and a new/edit record screen. Better yet, PowerApps will create and populate a form control with all of the custom fields ready to be populated. Based on the fields configuration it can auto create mandatory flags, error handling and hint text. You may question whether PowerApps has some limitations due to not having a code editor, whilst I’m sure some will find this to be true, I am yet to be disappointed. Instead of code, PowerApps uses Excel like functions and context variables which will feel very intuitive to any excel user. Context variables get stored at App level and can be called and updated from anywhere within your App. When creating the App, you can choose from a range of controls including Power BI tiles, Star Ratings, PDF viewers, Import/Export, the list goes on. Additionally, the gallery options mean you can display data or images in a real variety of ways. Above all that though is the integration with the devices media capabilities that make PowerApps a really cool product for non-coders. With PowerApps you can take and save pictures, Play and record video/audio and even scan barcodes. I’ve made a few basic changes to my App that you can see below but even if you hit F5 and previewed your app straight after creating it, you could successfully view, edit and input data to the database. So far I have written no code and Dan in IT is now free to go back to work. 1. Quick Actions: PowerApps has automatically created these quick actions to submit or close the form 2. Mandatory Indicator: Depending on the “Required” Property in the CDS 3. Text Box: In New mode will be blank, In Edit mode will show data. Can also show hint text and error messages if input is invalid. 4. Star Rating Control: I swapped a standard integer input with a star rating to make the App more user friendly.   Creating a Flow By default a newly built app is configured to write data back to the datastore by using a SubmitForm() function. These functions are handy for a lot of things as they take care of resetting the form after submission but also setting the form to Edit or New mode. If we want to do anything more than this – avoiding code – then we need to start looking at Flow. Flow can do an awful lot – just look at the pre-built templates for some ideas, but I’m going to use it to call the Glassdoor API to get job progression information. To create a Flow, you need to start with a trigger. The same goes for Logic Apps only, with Flow, you can trigger the process from a button press within your PowerApp. From then on you can create either actions, loops, branches, conditional logic and constraints in order to connect up any number of systems. 1. Trigger: Trigger point that is called from PowerApps 2. Initialize Variable: Passes a parameter from PowerApps into a variable to be used within the Flow 3. HTTP: Uses HTTP GET method to call the Glassdoor Job Progression API 4. Parse JSON: Parses the JSON response from Glassdoor and provides results in the form of variables 5. Email on Failure: By using the Run After feature I have configured an email notification if the Glassdoor API call fails 6. For Each Loop: Iterates over the JSON results and writes each set of variables to the database. At the moment I am using SQL so I can feed Power BI, the PowerApps team are working on deploying the CDS connector for Power BI to the UK in the coming months The formula that is used to call the Flow from PowerApps look like this: GetFutureJobs.Run(Occupation); Navigate(Results, ScreenTransition.None, {CurrentJob: Occupation}) In here there are 2 functions. The first (GetFutureJobs.Run(Occupation)) is the function to execute a Flow. Anything within the brackets will be passed into the Flow and can be used at any point within your process. In this case I pass in the users current job and use that to search Glassdoor for potential next jobs. Next is the Navigate function. This is a common occurrence in PowerApps and is used to take the user to the results screen. The first parameter is the target screen, Results. The second tells PowerApps how to transition between screens and the final array (the bit between these {}) is a list of parameters that can be passed into the next screen.   Implementing a Power BI tile The final step for my App is to analyse the results from Glassdoor using a Power BI tile. By creating a simple report and dashboard my PowerApp now has a fully functioning Power BI tile that will refresh on the same schedule as the main Power BI report within the service.   Hopefully from this blog you can see how powerful these two services can be when paired together but also how accessible these tools are now. The fact that I can have a working mobile app within minutes is somewhat revolutionary. I can certainly see a load of opportunities for these to be used and I encourage anyone reading this to have a play and unleash the POWER!