03-03-2022 22:40 PM - last edited 03-06-2022 16:48 PM
How to automate routine reports from data collection to visualization using Power Automate Cloud Flows, Power Automate Desktop Flows, SharePoint, and Power BI.
Works across any site, supported or unsupported in Power BI, and does not require any APIs.
_____________________________________________________________________________________________________________________________________
VERSION 2
(Updated for improved read-ability, set-up, & use-ability. The V2 cloud flow now works with both CSV & Excel Desktop flow set-ups. The delimiter change was moved from the cloud flow into the Desktop flows. Desktop flows are set-up to more dynamically adjust to your use-case with more input variables if you set them up on the Desktop side, but you will still need to map the actions for your sign-in & file download. All the cloud flow settings are more easily adjusted within the single "settings" action.)
Download The V2 Template Report Automation Cloud Flow From This Link or From The V2 Comment Below The Post (Works for both V2 CSV & V2 Excel Desktop Flow set-ups):
https://drive.google.com/file/d/1riqdmvwOfyUxNH_15m1gHlGUS_hZsOjV/view?usp=sharing
How to import a cloud flow: https://powerautomate.microsoft.com/en-us/blog/import-export-bap-packages/
Get Text Files With The Power Automate Desktop Flow Code From These Links. Select All The Code, Copy It, Then Paste It Into Your Power Automate Desktop Flow Editor as a Subflow (Also included in initial Compose actions of the V2 flow:
Main CSV Desktop Flow: https://drive.google.com/file/d/118A0u3Sz7C9S1AsvBnqYz41Zy6kC0Mbh/view?usp=sharing
Error Handling CSV Subflow: https://drive.google.com/file/d/1Sbo2xKPTvUrDcMV8X8WA0QzlUpB-SnXR/view?usp=sharing
Main Excel Desktop Flow: https://drive.google.com/file/d/1LlqUEo9YvgqyuF2-lir1wwNCSz17Ha3B/view?usp=sharing
Error Handling Excel Subflow: https://drive.google.com/file/d/1TkUzHfZnWnPINFqvfQ6WZ_wLD6oswEjW/view?usp=sharing
_____________________________________________________________________________________________________________________________________
VERSION 1
(As shown in the video.)
Download The Template CSV Report Automation Cloud Flow From This Link or Get The Attachment at The Bottom of The Post
https://drive.google.com/file/d/1k4xzu4DhrlmiZM0jU4yRxH1aOIFkt-rL/view?usp=sharing
How to import a cloud flow: https://powerautomate.microsoft.com/en-us/blog/import-export-bap-packages/
Get Text Files With The CSV Power Automate Desktop Flow Code From These Links. Select All The Code, Copy It, Then Paste It Into Your Power Automate Desktop Flow Editor as a Subflow:
Main CSV Desktop Flow: https://drive.google.com/file/d/1ZvS5gvMqADutrGQPwTyQIxaFIXEtiLb2/view?usp=sharing
Error Handling Subflow: https://drive.google.com/file/d/1G5LT12TXHZshk_VrmnObnS_5S674vNNo/view?usp=sharing
Change CSV Delimiter Information
***If the site you are working with only has Excel, not CSV download options, then go to my comment below on how to adjust the version 1 process to work with Excel documents.***
_____________________________________________________________________________________________________________________________________
TachyTelic.Net Batch Create Blog & Videos
Batch Create Flow Blog: https://www.tachytelic.net/2021/06/power-automate-flow-batch-create-sharepoint-list-items/
Video: https://youtu.be/2dV7fI4GUYU
If you are unfamiliar with Desktop flows, this does require a Power Automate license with RPA/UI Flow capabilities. Also my set-up here is using a virtual machine that is accessible from the cloud in a remote-viewer. The machine is always logged in so it can run an attended Desktop flow at any time.
watch?v=ONl7B9-9lPU
If your website only has Excel downloads and you are using the ReportAutomationTemplateV1 materials, then you will need to adjust a few things or download the Excel templates at the bottom of this reply...
(Or use the Version 2 cloud & desktop flow set-ups)
To adjust the original CSV template
You will need to download the Excel file, launch it, pull all the data from it into a DataTable variable, write that variable to a new CSV file with a new custom delimiter, then read that CSV file using a Read text from file action:
THEN
Because the output data in the Desktop flow variable will already have the new delimiter, you can just pull the Compose Line Break and Compose New Delimiter actions from the Scope Change CSV Delimiter action and delete the rest of the scope:
Then change the "Compose Reformat back to CSV with new delimiter" outputs in the GenerateSPData action to the plain Desktop flow variable output:
To download the pre-made Excel template
Follow this link or get the attachment at the bottom of this reply for the Excel template cloud flow.
https://drive.google.com/file/d/1jLlmRD5BsjwlCMnLD0fxyOgbwACdpyef/view?usp=sharing
Here is a template Power Automate Desktop flow for loading the Excel file contents to the cloud flow. This one does not use the direct file download URL method in case you want an example for sites that don't have that functionality. Select All The Code, Copy It, Then Paste It Into Your Power Automate Desktop Flow Editor as a Subflow:
Main Excel Desktop Flow: https://drive.google.com/file/d/13ZVHFRXrPrRQDngf21AoWvFI4fCY3LVb/view?usp=sharing
Error Handling Subflow: https://drive.google.com/file/d/1xU1Pps1weq15wy2awBTWAVZPm5SH9V4C/view?usp=sharing
VERSION 2
(Updated for improved read-ability, set-up, & use-ability. The V2 cloud flow now works with both CSV & Excel Desktop flow set-ups. The delimiter change was moved from the cloud flow into the Desktop flows. Desktop flows are set-up to more dynamically adjust to your use-case with more input variables if you set them up on the Desktop side, but you will still need to map the actions for your sign-in & file download. All the cloud flow settings are more easily adjusted within the single "settings" action.)
Download The V2 Template Report Automation Cloud Flow From This Link or From The Attached Zip File At The Bottom Of This Post (Works for both V2 CSV & V2 Excel Desktop Flow set-ups):
https://drive.google.com/file/d/1riqdmvwOfyUxNH_15m1gHlGUS_hZsOjV/view?usp=sharing
How to import a cloud flow: https://powerautomate.microsoft.com/en-us/blog/import-export-bap-packages/
Get Text Files With The Power Automate Desktop Flow Code From These Links. Select All The Code, Copy It, Then Paste It Into Your Power Automate Desktop Flow Editor as a Subflow:
Main CSV Desktop Flow: https://drive.google.com/file/d/118A0u3Sz7C9S1AsvBnqYz41Zy6kC0Mbh/view?usp=sharing
Error Handling CSV Subflow: https://drive.google.com/file/d/1Sbo2xKPTvUrDcMV8X8WA0QzlUpB-SnXR/view?usp=sharing
Main Excel Desktop Flow: https://drive.google.com/file/d/1LlqUEo9YvgqyuF2-lir1wwNCSz17Ha3B/view?usp=sharing
Error Handling Excel Subflow: https://drive.google.com/file/d/1TkUzHfZnWnPINFqvfQ6WZ_wLD6oswEjW/view?usp=sharing
Do you know yet the logic to add new columns using this flow?
Hello @jaeiow
I'm sure Power Automate Desktop has several options for adding columns to an Excel table: https://chercher.tech/power-automate-desktop/excel-automation-power-automate-desktop#:~:text=Insert%....
Looks like we will be able to upload a file directly to SharePoint now. This should remove any performance bottlenecks or input limits of passing the CSV in this template to the flow. Instead we can have a PAD flow end at creating the CSV file in SharePoint and have another flow trigger when the new file is added to a specific folder.
Don't need to login/authenticate to access the external report data? Maybe this cloud flow HTTP set-up will work for you without any Power Automate Desktop!