Automating Power Query Steps

[Phishdawg]

Once a Power Automate flow is built it can work automatically and without manual intervention.

I have created a flow that is populating a SP List when a new document is placed in a folder. All works well.

The source data is an Excel workbook with multiple sheets. Each sheet has the same 'unwanted' first seven rows. A version of the workbook - with the same name, arrives in the folder several times a week.

I have learned, and successfully used, Power Query to remove those seven rows, create a table, and transform the data.

I did all this one key stroke and mouse click at a time. Is there a way to, also, automate the Power Query portion of this task?

Or, will someone have to manually perform the Power Query portion on each workbook everytime a new one arrives?

[#2]

@Phishdawg 

With Power Automate you trigger the file (new/modified) and next you may rum OfficeScript which will do all transformations within the file. Sure, first you need to create such script first.

[#3]

Which is different than Power Query, yes?

If yes, do you have any suggestions of video to watch / article to read on "scripts" that will assist me?

[#4]

@Phishdawg 

Yes, that's totally another tool. With Power Query you may try to publish Excel file on Power BI if query is loaded both into the grid and data model. When in flow to refresh such dataset.

With OfficeScript the file shall be on OneDrive or SharePoint. And Automate tab in Excel shall be available for your subscription.

Next, you may google something for "Excel OfficeScript do that or this", lot of samples.

Finally, if you provide sample Excel file and explain what OfficeScript shall do with it, I could try to generate the script.