24 Days of PowerPlatform – Day 9 – Power BI and PowerApps

Welcome to another edition of #24DaysPowerPlatform!

In this post I would like to give you an overview about the Power BI and PowerApps integration. As you maybe already know, Power BI supports the mechanism of custom visual which allow report developers to add visual not shipped with Power BI to their reports. The list of custom visuals is growing and growing. Those visuals are developed by community developers, companies and also Microsoft adds custom visuals to the Power BI Visuals marketplace.

One of those custom visuals is the PowerApps custom visual which allows you to embed a PowerApp into a Power BI report and/or dashboard.

Example: Show Article Details in a PowerApp integrated in a Power BI report

Let’s start with an example report that contains sales information. The sales table itself contains the following fields:

The report looks like this:

What I want to to add now is a PowerApps that displays additional Article information (stored in an Azure SQL Database table).

Add the PowerApps visual

Configure the visual

Whenever you add a new PowerApps custom visual to your report canvas it needs at least one mapped data field – otherwise it will render as an empty visual.

Although you can add the PowerApps custom visual either in Power BI Desktop OR the Power BI Service – only in the Power BI Service you are able to create a new PowerApp with the report.

Create a new PowerApps (Power BI Integration)

After publishing the report to PowerBI.com, the visual content looks like this:

You can choose in which environment the new PowerApp will be created. In my demo, I’ll use the option “Create new”. A new browser window with the PowerApps Studio opens.

When creating a new PowerApp using the Power BI integration, you get an additional data source – PowerBIIntegration that serves as the connection to the Power BI report. Whenever a filtering action occurs in the Power BI report, this information is available in this property.

During the PowerApps creation action I selected the action to add a new form which in the next step needs to get a connection to the Article table (which holds the additional article details).

Create a new data source – In my case it is a connection to a Azure SQL Database.

 

Select the table you want to connect to.

In the next step, select the fields you want to display in your PowerApp form.

The result looks like this:

On IMPORTANT step is missing. The object PowerBIIntegration can contain a list of mapped Articlenames (in my example). The form itself can only display ONE item at a time. What needs to be done now, is to filter the Item property of the form to display the first selected entry and only show those pieces of information.

This filtering is done by setting the ITEM property of the form and assigning the formula:

This formula combines the data source from the PowerApp ([dbo].[Article]) with the PowerBIIntegration object. The first entry in this object is used for the filtering.

Save and Share the PowerApp

Before the PowerApp can be used in Power BI it needs to be saved and shared.

 

After this step you can switch to the PowerBI window and you can test the PowerApps integration in action.

First test case: no article selected – the first entry (alphabetical order) is selected.

If you select another article, the filter information is passed to the PowerApps visual and inside that the PowerBIIntegration object is used to set/filter the Item property of the Form.

 

With that, one of my first examples of the Power Platform in action is done!

I hope you enjoyed the sample!

 

This post is part of my 24DaysOfPowerPlatform blog series. Have a look at the other blog posts as well..

Advertisements

24 Days of PowerPlatform – Day 8 – PowerApps and Flow

Welcome back to 24Days of PowerPlatform!

After the first introductory posts describing the foundation pillars of the Power Platform, I would like to give you some ideas what is possible when using one or more parts working together.

Today, I would like to show you the team of PowerApps and Microsoft Flow.

When building a PowerApp, you sometimes have to interact with external systems. For those action, the Power Apps Studio provides you a native action for linking and executing Microsoft Flows.

In order to show you the possibilities I would like to create a PowerApp serving users to request office supplies.

Navigate to https://powerapps.microsoft.com and sign-in with your Office 365 user.

We’ll start with a new Canvas app from blank

PowerApps – Create a new application

Prepare the form like in the screenshot

PowerApps – Office Supply Request App

Some remarks:

• I like to name the controls! It gives you an easier approach for selecting the right control (see left part of the screenshot)
• (important) properties for the controls:
  • txtProduct -> Set default value, Format = Text
  • txtQuantity -> Default value = 1; Format = Number
  • txtRequester -> Display Mode = View PLUS setting the default value using a formula
    • Advanced -> Data -> Default: User().Email

Properties for txtRequester

 

• The next step is to create an action that is fired after the “Send Request” button is clicked.
• Select the Button and open the “Action” menu -> Flows

PowerApps – Add a flow

• Create a new Flow

Create a new Flow

A new Browser window opens and the Flow Editor is displayed

The newly created Flow

The created flow already has a PowerApps trigger that does not require any configuration (the binding from the PowerApps form to the Flow is automatically generated).

What is missing now is a next step for the Flow. I would like to store the request information into an Excel file stored in SharePoint Online (structure example see below). The Excel data is formatted as a Table.

The Target Excel

 

Add a new Step and Select the Excel Online (Business) Connector and the “Add a row into a table” action

Select the Action

As a first step, we need to configure the connection to the already existing excel file.

Configure the target Excel

 

Next step: Map the Excel table fields to the PowerApps fields. Just click into a field and then use the action “Ask in PowerApps”

Map the field content

The final mapping should look like this:

Final mapping

The Field RequestAdded is not mapped to a PowerApps field but to an expression (utcNow() returns the current timestamp)

Save the flow and return to the PowerApps browser window. Select the new created flow.

After some seconds the connection is added and you need to finish the configuration with the binding of your PowerApps fields to the input parameters of the Microsoft Flow. This can be done in the formula field. PowerApps already creates the initial execution stub – your part is now to add the parameters

The start of the formula to execute the Flow

Add the mapping to the Text properties of the appropriate input fields. If you’ve done it correctly, the formula bar looks like this:

The final command

If your formula contains an error, the PowerApps “App Checker” gives you a hint what is wrong with your formula.

PowerApps – App Checker

But now we assume, the the formula is correct and the next step is to test the app. Press the “Play” button in PowerApps is the easiest way to directly test it.

Test the PowerApps

Hit the “Send Request” button and after some seconds a new entry should appear in the target Excel table.

The entries are added into the target Excel file

If the new entries do not appear, you can have a look at the flow execution history to get an idea why it did not work. Browse to  https://flow.microsoft.com, open “My flows” and then the created Flow.

Flow – Dashboard

This dashboard provides you with the information you need to know about your Flow:

• Connections to external systems
• Security information
• You can enable/ disable your flow
• You can switch to edit mode
• And you can have a look at the run history… In my case there were no errors, but with a click on a certain run history row you’ll get more details.

 

With that, the first example of a Power Platform solution is done. To conclude, PowerApps and Flow are a well-integrated team and this approach makes it easy for PowerPlatform developers to create powerful solutions! 😊

 

This post is part of my 24DaysOfPowerPlatform blog series. Have a look at the other blog posts as well..

24 Days of PowerPlatform – Day 7 – Tell me more about the PowerPlatform Future!

Hi & Welcome back to 24Days of PowerPlatform!

Today I would like to write some paragraphs about the future direction and planned feature for the Power Platform. In the past, it was quite challenging to get information about the future strategy and planned features of different products.

It was sometimes like a look into a crystal ball to guess some upcoming features 🙂

source: https://www.pexels.com/photo/crystal-ball-in-hands-6101/

 

But the way how Microsoft communicates those future plannings to customers has changed!

For the PowerPlatform and all the included products

• Dynamics 365
• Power BI
• PowerApps
• Microsoft Flow and the
• Common Data Service

there is now a one-stop information point for planned features:

You’ll only have to remember one URL: https://aka.ms/businessappsreleasenotes for all the information about the Power Platform. As of today, there are two version available – the Spring’18 and the October’18 release. The October’18 release deals with the new features that are planned to release between October 2018 and March 2019.

Be aware – there is a PDF and an online version available.

Be aware – these release notes are not static – there are frequent updates! So visit the page frequently to get the most current news!  Hint: there is a Change History available

Have fun and a look at the future of the #PowerPlatform!

 

This post is part of my 24DaysOfPowerPlatform blog series. Have a look at the other blog posts as well..

24 Days of PowerPlatform – Day 6 – How and Where to Start?

Hi & Welcome back to 24Days of PowerPlatform!

In the past days, I’ve introduced the members of the #PowerPlatform family: Power BI, PowerApps, Microsoft Flow and the Common Data Model / Services

During the summer I started a link collection dealing with the different aspects of the Power Platform – ranging from documentation, licensing to community links!

If you are interested to learn more, please have a look at my #PowerPlatform link collection!

Power Platform Link Collection

If a link is missing – please ping me and I will add it to the list!

Have fun and Happy #PowerPlatform-ing!

 

This post is part of my 24DaysOfPowerPlatform blog series. Have a look at the other blog posts as well..

MS Connect() 2018 – Data News

Yesterday was #MSFTConnect 2018 time! The event was an online event and the sessions were broadcasted live and are now available for on-demand viewing.

The starting points for #MSFTConnect 2018 are https://news.microsoft.com/connect-2018/ and https://azure.microsoft.com/en-us/connect/

The Connect() team published a Book of News (available as PDF)

The official Connect() 2018 blog post

There were many announcements – I will summarize the data related news for you:

• Azure Machine Learning Service General Availability
  • A look under the hood
• Azure Notebooks – Connect 2018 News
• Azure Cosmos DB Shared Throughput Offer General Availability
• Azure Cognitive Services Updates
  • Language Understanding (LUIS) Container Support
  • Custom Translator Support for MS Translator Neural Machine Translation
• Azure Stream Analytics on IoT Edge is GA
• Updates for the Azure IoT Device Simulation Solution
• Azure IoT Remote Monitoring Solution Updates
• Azure Maps SKU News
• Azure Time Series Insights – Public Preview (new features)

 

And – not to forget: Microsoft open-sourced Windows Presentation Foundation (WPF), Windows Forms and Windows UI XAML Library.. wow!

On-Demand Sessions

The #MSFTConnect 2018 sessions are available for on-demand viewing:

direct link: https://azure.microsoft.com/en-us/resources/videos/index/?series=connect-2018&sort=newest&tag=pre-event-videos

Have fun,

Wolfgang

 

 

24 Days of PowerPlatform – Day 5 – What is the Common Data Model?

Hi & Welcome back to 24Days of PowerPlatform!

If your job includes building business applications, it is highly possible that you’ve re-implemented some sort of data models or entities in a nearly identical shape. That nearly identical approach is a possible one but not a recommended one. Maybe you and your colleagues have implemented a company standard data model and you are using that model for all your applications.

What is the Common Data Model?

A similar initiative was started by Microsoft – and they have already done a huge amount of work. The Common Data Model (CDM) defines a

• standardized,
• modular and
• extensible collection of data schemas dealing with business applications data.

The CDM is published by Microsoft and publicly available from the Microsoft CDM Github repository (https://aka.ms/cdmrepo) .

The Common Data Model defines a collection of entities having attributes plus additional semantic metadata and relationships between those entities. The CDM contains a huge number of entities (~160 entities and growing) used for business applications (like Account, Contact, Address, ..). CDM entities are organized in modules (Core, CRM, Healthcare, ..).

IMPORTANT: the CDM entity structure is extensible – if your company applications Account structure need some more fields, use the CDM Account structure as a starting point and add the needed fields.

HINT: If you want to browse through the CDM structure – the CDM team provides a graphical CDM repository browser (https://microsoft.github.io/CDM/)

CDM repository browser

The CDM team prepared a CDM Poster (which is free for download here – https://aka.ms/cdmposter ).

CDM Poster

What is/a the Common Data Service?

The Common Data Model does NOT..

• .. provide a storage mechanism like a DB
• .. include the “actual” data of the entities
• .. imply a level of security or compliance

For all those requirements, you need an implementation of the Common Data Model and one example is the Common Data Service (CDS) provided by Microsoft. Within the Microsoft data universe there are several implementation of a Common Data Service, like …

• … the Common Data Service for Apps (CDS4Apps) which is used by PowerApps. For this implementation you need to create a CDS Database in your PowerApps environment.
• … Common Data Service instances in the context of Dynamics 365 (example – Customer Engagement)
• … Power BI dataflows are using a Common Data Model schema to store the entities filled by the dataflows

CDS4Apps (in PowerApps)

Why use the Common Data Model?

The CDM improves the application development by providing a common data schema based on common sense (at least Microsoft common sense) but is not fixed to this proposal – if you need extensions to CDM structures, just add them.

I don’t want to repeat the benefits from the documentation site, so I just add them here:

• Structural and semantic consistencyacross applications and deployments.
• Simplified integration and disambiguation of datathat’s collected from processes, digital interactions, product telemetry, people interactions, and so on.
• A unified shapewhere data integrations can combine existing enterprise data with other sources and use that data holistically to develop apps or derive insights.
• Ability to extend the schema and CDM entities to tailor the CDM to your organization.

Open Data Initiative (ODI)

In September 2018, at the Microsoft Ignite conference, three big players – Adobe, Microsoft and SAP announce the Open Data Initiative to even broaden the Common Data Model from not only Microsoft products but also to extend it to other vendors. The core focus of the Open Data Initiative is to eliminate data silos and enable a single, comprehensive view of data.

source: https://www.microsoft.com/en-us/open-data-initiative

Documentation

• The Common Data Model
• CDM Doku @ GitHub (http://aka.ms/cdmrepo)

• Interactive CDM repository explorer (https://microsoft.github.io/CDM/)
• Open Data Initiative (https://www.microsoft.com/en-us/open-data-initiative)

 

This post is part of my 24DaysOfPowerPlatform blog series. Have a look at the other blog posts as well..

 

24 Days of PowerPlatform – Day 4 – What is Microsoft Flow?

Hi & Welcome back to 24Days of PowerPlatform!

Imagine a world of systems that are not connected, that are not exchanging data, that are working independently!

Is this a real scenario? I don’t think so.. In our world of connected systems there is a huge request for connecting systems altogether.

In former times, these data exchange, data integration or workflows could only be created by IT departments or experts. Also the process of creating workflows (for example to react on new files stored in a Sharepoint list or to start an approval workflow) was done by experts.

With Microsoft Flow, the workflow world changed! Microsoft positions MS Flow into the field for “citizen workflow” – which means not only experts but (almost) everyone should be able to create workflows!

Based on a web-based user-friendly UI, workflows can be created with a low / no-code approach. The Flow editor is available at http://flow.microsoft.com.

Every MS Flow starts with a trigger that are followed by some actions.

Examples for triggers are for example whenever a new file is created in a Sharepoint List, a new data row is inserted into a database, a new tweet for a certain hashtag is published or ….. The list of available connectors is long (detailed list) and every source system provides its own set of triggers. For example, the new file trigger is not available in the Twitter connector.

After the initial trigger action in the flow, the process flow can be controlled by dependency relationships (like action 2 is running after action 1) but you can also control the flow in the workflow based on certain criteria (if this .. then that .. else OR defining loops).

Outputs of former actions/triggers are added to the control flow and can be used in subsequent tasks (for example: trigger “a new file is created” -> the information about the file is available as parameter values in subsequent actions and can for example be written into a database or added to a mail text).

Flows can be created as personal flows but the best practice approach is to share your flows and make them to team flows (= removes the single responsibility problem).

When your flow development is done you can enable the flow and after that the flow reacts to the defined trigger event.

Integration (Sharepoint, Excel Online and PowerApps)

There is a tight integration of MS Flow workflows into Sharepoint Online. Whenever you are working with a document library / Sharepoint list, you can add a Flow to react on certain triggers within that list.

In Summer 2018, Excel Online got a new Add-In – the Microsoft Flow Add-in. This Add-in serves as integration point for starting Flows directly out of an online hosted Excel workbook. The flows can be applied to / started for a single row OR you can select multiple rows and start the flow for multiple row instances.

Another point of tight integration is between PowerApps and Flow. You can bind the action of a button in a PowerApps directly to a Microsoft Flow. Whichever action is not directly supported out-of-the-box in PowerApps – with an assigned Flow you can just do it!

Templates

The Flow team provides a huge list of Flow templates that can be used with a single click. Just open the flow.microsoft.com site and browse through the template gallery, select the template, enter the credentials to the systems involved and – et voila – the Flow is ready!

What I can recommend to you is to start with a Flow template, instantiate it and then go into research and extend the pre-build ones! This was a huge learning point for myself!

Mobile App

Microsoft Flow is also available on your mobile phone (Android and iOS and Windows Phone). What makes the mobile version special is the possibility of authoring Flows on your mobile device!

Direct Links

• Android: https://aka.ms/flowmobiledocsandroid
• iOS: https://aka.ms/flowmobiledocsios
• Windows Phone: https://aka.ms/flowmobilewindows

In addition, the concept of Flow buttons allow you to trigger Flows directly out of your Flow app. Only two steps required: 1) define a flow, 2) create a flow button!

 

Licensing

Legal info at last – here are the links to the licensing points for Microsoft Flow

• https://flow.microsoft.com/en-us/pricing/

 

Flow Documentation

• https://docs.microsoft.com/en-us/flow/

 

 

This post is part of my 24DaysOfPowerPlatform blog series. Have a look at the other blog posts as well..