Power Platform : Export filtered data table records to CSV file from PowerApps using Power Automate
Hope you all are doing well.
Today in this article I am going to discuss about how to export filtered data table records to CSV from PowerApps using Power Automate.
We have good number of articles on Power Platform, please have a look.
So without getting late, lets get started.
Having a dashboard screen with data table control and with all required filter options is a very common use case in PowerApps app. For every process such dashboards are required so that users can view and manage the records. Sometimes users need those records with all the columns from the backend to be exported to an excel file so that they further analyze the data easily using excel features.
There is no direct connector in PowerApps to export the data to an excel file, so we must trigger a FLOW from PowerApps and generate the CSV file in OneDrive or SharePoint and respond back with URL of the generated file.
First Approach with limitations
Iterate through gallery items using concat and concatenate functions in PowerApps, generate a JSON string and pass it to FLOW. In the FLOW, parse the JSON string -> Create CSV file and respond back the URL of CSV file.
Limitation with this approach would be – if there are more than 100 items as a result of our filters, then user needs to scroll down till it loads all the items in gallery, then only he can export all filtered records. If user doesn’t scroll down to load all filtered records in gallery, then it will only export first 100 items. This is because PowerApps uses on demand loading of data, it loads data in batch of 100.
Second approach without limitations
This is the approach we are going to look at in this article. In this approach user doesn’t need to worry about if all filtered items are loaded in gallery or not, because we are not sending the items data, we are passing filter parameters to the FLOW.
The Errors app is used by people at an office to create a report based on the severity level of error. They open the app to the Error details screen, click on a button to download and then open the CSV file in Excel.
Setup the SharePoint list
- Create a new SharePoint list called Errors with the following columns:
|Title||Single line of text|
|Error from||Single line of text|
|Error message||Multiple lines of text|
|Cause||Multiple lines of text|
|Resolution||Multiple lines of text|
- Include this data in the list.
Insert a table to display information in the app.
- Open Power Apps Studio and create a new app from blank. Place a label at the top of the screen showing the title.
- Add the Errors SharePoint list as a datasource.
- Then insert a data table with and update the Items property to ‘Errors’ to show the list of error details.
PowerApps data table filter
Filter data table based on SharePoint choice field.
- In this scenario, we will see how to filter the PowerApps Data table based on a SharePoint Choice column. Here we will take the SharePoint List (Errors).
- In that list, there is a Choice column named Severity. This column has some values like Low, Medium, and High as like below.
- On the PowerApps screen, there is a Dropdown control having with the below Items property:
Items = Choices(Errors.Severity)
- Next, apply the below code on Data Table’s Items property as:
Items = Filter(Errors, dd_Severity.SelectedText.Value = Severity.Value)
dd_Severity = Dropdown control name
- Save and preview the app. Select any severity choice value from the dropdown control, then we can see the filtered choice result in the data table as shown above.
Creating the export to excel flow.
- Now we are ready to make the flow to convert the errors table data to a CSV file. Go to the Action tab and select Power Automate. Then click create a new flow.
- Refer to the below screenshot to understand the steps used in FLOW -> Taking filter values as parameters from PowerApps.
- Use the severity variable output/result as input to Get Items as shown below -> Use create table action to generate tabular data, add whatever columns we want to export to CSV file.
- Use OneDrive create file action to create a CSV file, then use create share link by path action to generate the CSV file URL, respond back this URL to PowerApps.
- At last, to export the filtered data to Excel sheet, insert an icon Download. Select the icon and apply the below formula on its OnSelect property as:
Concurrent (Notify ("Working on it...", NotificationType.Information), Set (csvFileURL, ExporttoCSV.Run(dd_Severity.Selected.Value).fileurl));Launch(csvFileURL);
- We have triggered a FLOW on export icon click. If we see the export icon click code, then we have used Launch function to download the CSV file for end user.
- This is how the exported data looks in the CSV file, the user can easily save it as Excel file and do the analysis.
Hope this article will help us to export the filtered data table records to a CSV file from PowerApps using Power Automate.
Also get my article updates on my social media handles.
LinkedIn – https://www.linkedin.com/in/khasim-shaik-8784a1232/
Twitter – https://twitter.com/KhasimShaik2009
Facebook – https://www.facebook.com/profile.php?id=100078255554660
Thank you for your support, will catch up with new article soon.
Keep learning and keep sharing.
Have a great day.
You must log in to post a comment.