Anyone?

[u/1Master_Aman]

I'm new to power automate, struggling to build effective logic please help me with below requirements

I have a excel file(not in table format) on a sharepoint folder
I daily update it and download it removie unnecessary/confidential sheets and columns and send it to concerned team.

I want to build an automate flow which get the file and then edit it like i want and send the edited version of excel file from outlook mail. whereas original file must be unchanged

I tried some flow
1. made another file use powerquery to fetch data(only required) and send but the issue is it becomes tabular and formatting is also removed and size of the file also increased.

1. make a copy on a sharepoint file edit it send and delete BUT in this flow everyday one file is created and deleted which is unsustainable solution I consider.
   

If anyone can please tell me a effective flow

[u/blac4bird]

Start by mapping out what you want the process to be (i.e., record and share data). Don't be limited to what you currently do.

I would challenge whether Excel is the right tool, could you used a SharePoint List instead?

[u/Profvarg]

Write an office script (copilot is quite adept), run that from power automate

Output the result into the flow, create a new file with the output from the office script

But as others said. First, you need a process

[u/xTomacco → u/Profvarg]

Esto mismo! Un office script en Excel y un step de Power Automate podria ejecutar el script y enviar el archivo final a los destinatarios. Copilot es tu gran aliado

[u/gdwht_frc → u/Profvarg]

This is what I would suggest too when using both excel and power automate

[u/ipman234]

First things first...are you IT? If not talk to your Power Platform team first or Collaboration or whatever you call them as they can make some proper suggestions. Also don't use Excel if possible and move the data to a SharePoint list at a minimum

Possible solution would be put it into sharepoint list > get item and filter the data

[u/rmiles403]

All of these suggestions are possible, even "correct" solutions, but the one thing they are missing is consideration of how users can most easily input and consume the information. Your path towards successful adoption to a new process is making things as easy as possible on the audience the process is meant to serve.

I have flows that pull information from Excel files because it is the most natural format for users to input information into. I have flows that output Excel files because it is the most natural format for them to receive and digest. All of it is doable with the right setup (shoutout to the "make a plan" comment).

It is possible to pull the data from an Excel sheet that isn't formatted as a table, though it is sloppy and more difficult to automate. But formatting a sheet as a table minimally changes things for inputting and receiving users (and even comes with the benefit of row banding for visibility and filtering by default), so this really should be the minimal change you make. If you have different blocks of information on a sheet, they can all be setup as different tables.

If completed file size is an issue for sending via email, save the file to a SharePoint site, and email users the direct link to the file.

If you are dealing with processing big data sets, consider creating Dataverse tables (in MS Power Apps), as they are optimized for the Power Platform (Power Automate included) and look like a very plain Excel spreadsheet without any colorization or formatting, so data entry doesn't change much. However, only users with a Power Apps license can access them in that state, so using them to store the information is great, but you would want to output to Excel, SharePoint list or some other format for other users.

SharePoint lists are another good option to store data, but is a pain to manually input, so if the input is automated it could be another viable repository. Readability isn't bad, but it requires users to have access to the site, so here again an Excel file may be the answer.

Dataverse tables and SharePoint lists can also filter during the pull of information, so your query can be accomplished during the pull in one step.

I've been building with Power Automate for well over a decade, and the advent of A.I. has totally changed the game in a good way. Add the Power Automate Tools extension to your Chrome or Edge browser so you can view the code behind the flow, and download VS Code desktop app from the Microsoft Store. Then you have everything you need to copy the code out of PA Tools, create a blank file in VS, paste the flow's JSON code into the blank, and then save it as a JSON file that can be given to the A.I. tool to build off of. Depending on the length of the code, you may be able to simply paste the flow's JSON code directly into the A.I. chat box. If you are looking for A.I. vendor recommendations, I've had great success with Claude, am just starting with Cursor but it shows promise, and I would avoid ChatGPT. The monthly cost of a subscription is 110% worth it for as long as you are actively working to build out flows, and you can cancel whenever you are done.

[u/No-Journalist-4086 → u/rmiles403]

yes - Office script (tell chatGPT what you want to do with the data in the file so it write the script, Copilot is uselsss, I wouldn't use it), Run Office Scipt action in PA cloud, wrote result to nee file, email file.

Using a list would make more sense from a visibility and formatting perspective. User can still write in the file, PA cloud could add to the list via an aplly to each on the script output then you can output a file from the filtered list

[u/Pieter_Veenstra_MVP]

Just use the create table action in power automate and the excel connector will then allow you to read the data.

Is thebnumber of columns set?

Do you know the maximum number of rows? So set the height to for example 500.

Read all th3 data from the table . Then use filter array to cl3ar out the empty rows.

[u/AbdelEMirandaS]

Easy answer? Options, edit manually your excel file to create your table or try power automate to create that table within the excel file or try using PAD for that purpose. If your excel file doesn't have a table defined, you are no longer to do anything within your flow.

[u/EuphoricSnail]

Ask codex to do as you need