Requirement
Having a dashboard screen with gallery 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 your 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.
This is the sample app with dashboard screen, filter options and gallery control.
Imagine you have applied the required filters and want to export this data to CSV file
We have triggered a FLOW on export icon click ->
Concurrent(
Notify(
"Working on it...",
NotificationType.Information
),
Set(
csvFileURL,
ExportTenantsData.Run(
projectsDropdown.Selected.Value,
contractDropdown.Selected.Value,
statusDropdown.Selected.Value
).fileurl
)
);
Launch(csvFileURL);
This is how the exported data looks in the CSV file, the user can easily save it as Excel file and do the analysis.
Refer to the below screenshot to understand the steps used in FLOW -> Taking filter values as parameters from PowerApps -> using compose function to build a query
We will generate query by using filter parameter values, this query would be used in Get items action to get filtered items only. Refer below function/expression which is used to generate correctly formatted query,
concat(if(not(equals(variables('Project'),'All')), concat('Project eq ''', variables('Project') ,''' and '),''),
if(not(equals(variables('Contract'),'All')), concat('Contract eq ''', variables('Contract') ,''' and '),''),
if(not(equals(variables('Status'),'All')), concat('ApprovalStatus eq ''', variables('Status') ,''''),''))
Another compose action is needed to remove trailing extra and operator, this is needed when not all filter parameters are used.
if(endsWith(outputs('Compose'),' And '), substring(outputs('Compose'),sub(length(outputs('Compose')),5), length(outputs('Compose'))), outputs('Compose'))
Use the above expression output/result as input to Get Items as shown below -> Use create table action to generate tabular data, add whatever columns you 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.
If you see the export icon button click code, then we have used Launch function to download the CSV file for end user.
Please check out this YouTube video to see above configuration in detail.
Summary
This approach is best suitable for large datasets, you can easily export the data into a CSV file and download it for end user within 3-4 seconds.
Hope this will help you guys. Thanks for reading.
Join the conversation! Your thoughts help the community grow.