{"id":42414,"date":"2023-04-04T08:54:51","date_gmt":"2023-04-04T12:54:51","guid":{"rendered":"https:\/\/centricconsulting.com\/?p=42414"},"modified":"2023-04-04T09:13:16","modified_gmt":"2023-04-04T13:13:16","slug":"power-app-and-power-bi-integration-with-write-back-and-reporting","status":"publish","type":"post","link":"https:\/\/centricconsulting.com\/blog\/power-app-and-power-bi-integration-with-write-back-and-reporting\/","title":{"rendered":"Power App and Power BI Integration With Write Back and Reporting"},"content":{"rendered":"

We walk through the steps of using write back and integrated reporting with Power Apps and Power BI in this blog.<\/h2>\n
\n

Microsoft\u2019s Power BI is a powerful platform that facilitates enterprise business intelligence (BI) with scalable, self-service tools. With Power BI, users can connect to and visualize data they can use to create attractive interactive visuals.<\/p>\n

By integrating Power Apps — Microsoft\u2019s native app — with Power BI, users can write back data. Power Apps<\/a> will display and allow users to interact with data from the Power BI report, including creating, editing and deleting data rows that update in real time.<\/p>\n

For example, we recently had a client that wanted to see the Power BI<\/a> report along with an app to update, insert and delete the data in the source or edit in line and report from one canvas. Before, users were not able to do so from Power BI.<\/p>\n

However, Power BI doesn’t currently have a native solution for inline or bulk data updates while interacting with a report or dashboard.<\/strong> That means to push changes to data, users must make updates directly in their data stores and then, if they\u2019re not using Direct Query (live mode), refresh a data set to complete the process flow. It\u2019s important to note that if Power BI is in Direct Query, users can see visuals updated in real-time.<\/p>\n

Either way, the process can be inefficient and pose problems for users who don’t have access to a specific back end or the underlying data. Fortunately, there is a better way to do this using write back.<\/strong><\/p>\n

Outlined below, the solution for the data write-back function provides an interactive and efficient way to change your data source directly from Power BI.<\/p>\n

Write Back to SQL in Power BI<\/h2>\n

Microsoft Power Apps is a key pillar of Power Platform<\/a>, which provides the write back to Power BI reports using Power apps Visual. It helps write the data back to SQL or any other data source used within their respective projects. This allows a Direct Query connection to Power BI and enables data visualization with the Power Apps live in the Power BI report. To do so, follow these steps:<\/strong><\/p>\n

    \n
  1. Create a Power BI report in Direct Query mode using interactive visuals per the business needs. Get data in Power BI and use tables or SQL Scripts.<\/li>\n
  2. For the write back function, use Power Apps Visual within Power BI.<\/li>\n
  3. Drag all required fields you need to update to the Power App visual.<\/li>\n
  4. The visual will ask you to create an app within make.powerapps.com<\/a> or choose from an existing app, as shown below:<\/li>\n<\/ol>\n

    \"Power<\/a> \"\"<\/a><\/p>\n

      \n
    1. Upon clicking Create new, users are directed to the Power Apps page. Power Apps will automatically create an integration object using the selected fields passed from the report.<\/li>\n<\/ol>\n

      Editing and Deploying Apps in Power Apps<\/h3>\n

      In Power Apps, users can create and edit forms based on specific requirements using various available options. You have to embed a Power App in your Power BI report to write back to the data source. The tree view shows the visual hierarchy of the components.<\/p>\n

      Since you have already established the data connection, Power BI uses the forms option to update the record in the database. Select and arrange all the required fields. Edit fields by selecting Fields > Edit Fields<\/strong> at the right corner, as shown below.<\/p>\n

      \"Issue<\/a><\/p>\n

      The use case may require making UI\/UX changes to the app, such as changing the header color or other design layouts. The above form also includes a Submit button at the end with the ON Select expression as SubmitForm (Form Name). Click Save to save the app. Microsoft then creates the app on the backend and adds it to the Power BI report, as shown below.<\/p>\n

      \"Issue<\/a><\/p>\n

      Deploy your final Power BI report to service with interactive visuals and the app created. Shown below is the report published with the Power App embedded as described.<\/strong><\/p>\n

      \"Issue<\/a><\/p>\n

      Drilling into the Data Source and Architecture<\/h2>\n

      Let\u2019s take a closer look at the data source and architecture. The data source is MS SQL Server. However, the approach would be the same for any other source, such as SharePoint, Dataverse, Synapse and so on.<\/strong><\/p>\n

      The Power App will instantaneously refresh the Power BI report and provide a virtually unlimited refresh scenario. The Power BI report will use MS SQL Server with Direct Query mode and not rely on scheduled refreshes or imports.<\/p>\n

      A major component of this solution is the ability to pass data from Power BI into Power Apps for updates. It is critical to refresh the Power BI dataset to ensure all users can see the updates. Here are the steps:<\/strong><\/p>\n

        \n
      1. Get data in Power BI by importing views and tables, using Direct Query or writing custom SQL queries<\/li>\n
      2. Create a Power BI interactive report and integrate Power BI and Power Apps via the Power Apps visualization in the Power BI desktop application.<\/li>\n
      3. Use Power Apps to create a canvas app, which will provide the ability to interact with and update all necessary data.<\/li>\n
      4. Test and publish your solution to Power BI Service.<\/li>\n<\/ol>\n

        \"Power<\/a><\/p>\n

        Other Potential Use Cases<\/h2>\n

        In addition to the examples above, other use cases could include:<\/p>\n