Can’t INPUT DATA in Power BI? Here is a WRITE BACK Option with Power Apps!

Summary

In this tutorial, the presenter, Boss, demonstrates how to use PowerApps to enable data write-back in Power BI, a feature not natively supported. The video guides viewers through creating a PowerApp visual in Power BI, connecting it to a data source, and building an application in PowerApps. It shows how to set up a form to edit data and submit changes back to the data source, with options for immediate visual updates using direct query or scheduled refreshes via Power Automate. The presenter also explores alternative methods like tooltips and drill-through for user interaction, offering a comprehensive guide to overcoming Power BI's data write-back limitations.

Takeaways

• 😀 Power BI does not support direct data editing within the platform; it only reads from data sources.
• 🛠️ A workaround for data editing in Power BI is to use PowerApps to write back to the data source.
• 📊 The PowerApps visual can be added to a Power BI report to facilitate data editing through an app.
• 🔗 Power BI and PowerApps have a special integration that allows for a seamless connection between the report and the app.
• 🏗️ In PowerApps, you can build screens and objects to create an application that interacts with Power BI visuals.
• 📝 To write back data, you can connect PowerApps to various data sources, including Excel files on OneDrive.
• 🔄 For real-time updates in Power BI visuals, a data source that supports DirectQuery is recommended over OneDrive Excel files.
• 🔗 Switching to a data source like SQL Server or Dataverse allows for instant updates in Power BI visuals upon data editing.
• 🔧 Power Automate can be used to create flows that trigger data refreshes in Power BI when changes are made via PowerApps.
• 💡 Alternative methods for data editing in Power BI include using tooltips, drill-through pages, or toggling between visuals and PowerApp forms using bookmarks.

Q & A

Why can't you directly edit values in Power BI like you can in Excel?

Power BI is designed to read from data sources and does not support writing back to the data source directly, unlike Excel which allows for direct editing of cell values.

What is one workaround mentioned in the script to enable data write-back in Power BI?

One workaround mentioned is using PowerApps to create an application that can write back data to the data source.

How do you add the PowerApps visual to a Power BI report?

You can add the PowerApps visual to your report by finding it in the visualization section and adding it to your report page.

What is the significance of the URL provided when creating a new PowerApp in Power BI?

The URL provided when creating a new PowerApp in Power BI is significant as it opens up the PowerApps interface in your browser, allowing you to start building the application that will interact with your Power BI report.

How does the Power BI integration work within PowerApps?

The Power BI integration within PowerApps allows for a special connection between your report and the app, enabling the use of fields from the Power BI report within the PowerApp.

What is the purpose of the form in the PowerApp created for the Power BI report?

The form in the PowerApp is used to change the selected entry in the Power BI table visual, allowing users to update data that can then be written back to the data source.

Why is it necessary to publish the PowerApp after making changes?

Publishing the PowerApp after making changes is necessary to update the app and make the changes available to the Power BI report, ensuring that the interaction between the two platforms is current.

How can you ensure that the form in PowerApps shows the information for the selected item in the Power BI table visual?

To ensure the form shows the information for the selected item, you can use a formula with the lookup function, referencing the selected value from the Power BI table visual.

What is the role of the 'submit' function in the PowerApp form?

The 'submit' function in the PowerApp form is used to submit the changes made in the form, which then can be written back to the data source.

How can you refresh the Power BI visuals to reflect changes made through the PowerApp?

You can refresh the Power BI visuals by using the 'power bi integration dot refresh' command within the PowerApp, which updates the visuals without needing to manually refresh the data in Power BI.

What are the limitations when using an Excel file on OneDrive as a data source for write-back in Power BI?

When using an Excel file on OneDrive as a data source, changes made through PowerApps do not automatically reflect in Power BI due to the import mode. You need to manually refresh the data, which is limited to eight refreshes for Pro license users.

What alternative data sources are suggested for direct query in Power BI to avoid manual refreshes?

For direct query capabilities in Power BI, alternative data sources like SQL Server or Dataverse are suggested, which allow for automatic updates in Power BI visuals when changes are made through PowerApps.

How can you use bookmarks in Power BI to toggle between a table visual and a PowerApp form?

You can use bookmarks in Power BI to create a toggle effect between showing the table visual and the PowerApp form. This allows users to view the data and then switch to the form to make edits without leaving the report.