Automate Reports From External Datasources 09-23-2022 09:43 AM

[takolota]

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

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Change-CSV-Data-Delimiter-From-a-Text-Fi...

***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

[takolota]

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 

[takolota]

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 

[jaeiow]

Do you know yet the logic to add new columns using this flow?

[takolota]

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

[takolota]

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.

[takolota]

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!

https://youtu.be/2kvSlh-Tvb4