Power Platform : Export filtered data table records to CSV file from PowerApps using Power Automate

.

Hello Everyone,

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.

https://knowledge-junction.in/category/technology-articles/power-platform/

So without getting late, lets get started.

Requirement

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.

Solution

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.

Introduction:

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:

TitleSingle line of text
Error fromSingle line of text
Error messageMultiple lines of text
CauseMultiple lines of text
ResolutionMultiple lines of text
SeverityChoice
fig: SharePoint list site columns

  • Include this data in the list.

SharePoint list with data
fig: SharePoint list with data

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.

PowerApps title
fig: PowerApps title

  • Add the Errors SharePoint list as a datasource.

PowerApps datasource
fig: PowerApps datasource

  • Then insert a data table with and update the Items property to ‘Errors’ to show the list of error details.

PowerApps datatable
fig: PowerApps data table

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.

SharePoint choice field
fig: SharePoint choice field

  • On the PowerApps screen, there is a Dropdown control having with the below Items property:

Items = Choices(Errors.Severity)

PowerApps dropdown items property
fig: PowerApps dropdown items property

  • Next, apply the below code on Data Table’s Items property as:

Items = Filter(Errors, dd_Severity.SelectedText.Value = Severity.Value)

Where,

dd_Severity = Dropdown control name

fig: PowerApps data table items property
fig: PowerApps data table items property

  • 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.

Create new flow
fig: Create new flow

  • Refer to the below screenshot to understand the steps used in FLOW -> Taking filter values as parameters from PowerApps.

Flow taking filter values
fig: Flow taking filter values

  • 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.

Flow - Get items and Create CSV table
fig: Flow – Get items and Create CSV table

  • 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.

Flow - Create and download CSV
fig: Flow – Create and download CSV

  • 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);

PowerApps download icon OnSelect property
fig: PowerApps download icon OnSelect property

  • 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.

CSV file
fig: CSV file

.

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.

Thanks.

Khasim Shaik

SharePoint & Power Platform Developer at OS InfoTech

You may also like...

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: