Unanswered

Can this be done with Power Automate (Flow)? Dynamic Query?

(0) Share

Report

Posted on by rdshibuya

Is there a way to create a flow that captures a table’s value in an excel file which every month has a new name and is saved in a different file?

Just to give a background, I work with auditing and every month we evaluate the adherence of the teams to our internal processes, we give a rating for each one of our departments based on a few criteria. These grading are captured and saved in a excel file named as follow: TEAM_MONTH_YEAR.xlsx and this file is saved in a folder with a similar naming: MONTH_YEAR. Inside the folder there are many other spreadsheets, so I’m wondering, how I can create an automation that goes every month into the correct folder, and capture the data (a table) from the correct file? The idea is to get a list with all grades and teams, so we can use to create PowerBI report to display the data to our board managers.

Note: These folders and files are placed in a SharePoint.

I’m fairly new to the Microsoft Power Apps world, so sorry in advance if I’m asking a silly question.

Please, let me know if additional details are required or if my question isn’t clear.

Thank you in advance for all replies.

Categories:

General topics

All responses (3)

Answers (0)

Sort by

ALFRED05 113 on at

Like
a
(0)

Report
Copy link

Link copied!

So you add rows to the same file everymonth? If that so I think is better you create a folder to store ONLY that file and connect the folder to powerBI directly.

Here is how to connect to a folder.
https://youtu.be/6dppVwCxtv4
rdshibuya 6 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @ALFRED05,

Thank you for your reply.

No, every month a new file and folder are created.
WillPage 2,339 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Yes you can do this. First you need to sort out out your logic to find the file, e.g. by using Get files and then Filter array and filtering based on Path contains the expression formatDateTime(utcNow(),'MM_yy')
This might be the more complex bit depending on how the source is structured. You might benefit from learning about Odata filter queries for this.
Ultimately you want to narrow down to a single file or a group of files and then process the next part within an Apply to each loop around the results with your filtered array as the input.
Inside the Apply to each you need to undertake a 2 step process with a couple of compose actions in between to get the information you want.
Firstly, add the action Get file properties and use an expression items('Apply_to_each')?['ID'] as the file ID. Then add Get Tables (Excel Online Business). Select the site and library. The File should be the Id property or the Identifier property of the previous get file properties action, whichever is available to you.

The output of this action is an array containing all the tables within the file. If your files only contain one table this is pretty easy. Add a compose action and put the formula in an expression as follows:

substring(first(body('Get_tables')?['value'])?['@odata.id'],add(indexOf(first(body('Get_tables')?['value'])?['@odata.id'],'items('),7),34)
Then add another Compose (let's call it Compose 2)

first(body('Get_tables')?['value'])?['id']
Now you want to do List rows present in a table. Select the Location and Document Library then in the File input you want the first compose output and the Table is the second compose. You will need to click Enter custom value on the latter before it lets you add dynamic content.

Now you have the rows you can do whatever you want with those, whether that's adding to a SP list or a Power BI streaming dataset. This is all done within the Apply to each.

Having said all that, you could just use the SharePoint connector directly in Power BI to find files and import the tables directly using Power Query.