I'm trying to automate the export of a Power Bi query onto a specific Excel document. My company is constantly running a manual report to update this Excel file and we're trying to set a level of automation to the task to save company time.
So far I've managed to fully automate the Power Bi query to the point where my PowerShell opens Power Bi, edits the query and runs the API call. At this point I'm attempting to copy the table from Power Bi to Excel and I've found a solution but I can't figure out how to automate some parts of the solution onto my Power Shell script.
Apparently the easiest way to copy the table over is to be in Power Bi and click from the report tab onto the data tab. Then right click anywhere on the table containing the data and select 'copy table' Once the table is copied the solution is as easy as going to the excel file then using a select all and paste it would replace the existing data, and my job is done.
My big issue is that I can't find a good way to use a right mouse click within PowerShell. For the left mouse I've been using autoit but when I consulted their documentation they don't appear to have a right mouse command.
I'm looking for some way to get that table copied or exported to excel. Either through a series of keyboard shortcuts that can be replicated in PowerShell, or an explanation of how to use right click through PowerShell so that I can access that copy table command.
The first parameter of the MouseClick command in AutoIt specifies the mouse button that is supposed to be clicked:
MouseClick("right", 500, 500)
... will perform a click with the right mouse button at 500|500. The reference for this function specifies even more options.
Unfortunately I have never used AutoIt via Powershell, but after looking at some examples I think that you could call it this way:
Invoke-AU3MouseClick -Button "right"