Power BI and COM

[PeterlFF]

Posting this here since I buried this at the end of another of my posts about a different subject.

I want to automate some tasks in Microsoft Power BI. I don't see a UDF for that so that means I need to COM for Power BI. I want my script to pause when it executes an action before continuing on and it is my understanding that using COM is the way to achieve this. I used the Excel and PowerPoint UDFs and those worked where the script paused to wait for an action to complete.

I don't see much of anything for Power BI or COM on this forum. From the help file I see I can sue the OLE/COM Object Viewer to help see the COM objects on my system. But it looks like it is for server 2003 so not sure it will work for Windows. I think the Windows 10 SDK may have the OLE/COM Object Viewer so I will check that out. A command line tool for automating Power BI was pointed out to me but it doesn't look like it can do detailed tasks in Power BI that I need.

If anyone knows of anything else, or can point me towards something that will help me with trying to automate power bi using COM it would be appreciated.

Thank you in advance.

Edited March 21, 2022 by PeterlFF

[orbs]

Power BI has an internal scripting functionality called DAX (similar to VBA in Excel, Word etc.). it has a familiar syntax and is quite powerful. i used that in the past, and if i ever need again that would be my first choice. Power BI also supports running Python scripts, as well as R and probably some others.

unfortunately, Power BI does not feature COM. automating Power BI by windows controls manipulation is also not trivial, and would require UI Automation if you  really, really, really must.

[PeterlFF]

@orbs Does something like creating DAX code inside Power BI workbook to automate actions inside Power BI, and then using AutoIT to open the power BI workbook and start the DAX code sound realistic?

I thought it might be helpful to explain what I want to automate in Power BI. I want to automate things like opening a workbook, applying a filter to a dashboard using an existing slicer, export to PDF, publish to Power BI online, apply a filter to the online dashboard using an existing slicer, download the online workbook, and I want to be able to time each of these actions.

Can Dax handle any of that?

Edited March 21, 2022 by PeterlFF

[orbs]

i'm fairly certain anything inside Power BI (desktop, online service, and interactions thereof) can be achieved with DAX.

regarding publishing to the online service, you don't need to export to a file as an intermediate - you can do that directly inside Power BI. have a look here and here for start.

i recommend you begin by introducing yourself to DAX on the desktop. use it to gradually replace the easier pieces of your tasks, like applying filters etc. as you go, you'll discover its potential and how to use it.

i don't think you'd require browser automation; but if you do, the WebDriver UDF is your solution.

 

 

[PeterlFF]

Thanks @orbs

[PeterlFF]

I finally figured out a solution to my issue with timing actions in Power BI Desktop where it is rendering charts etc on the screen. I used PixelChecksum in a while loop to see when the screen is done rendering. If it hasn't changed in 3 seconds (plus the time it takes to run the code) then we assume the rendering is done and calc the time it took.

 

Local $hTimer = TimerInit()
         GetTime()
         $T1[$TR][4] = $GTime

         ; end time for open online Report
         Local $ic = "0", $ict = "0"
         While $ic < 30
            Local $iCheckSum = PixelChecksum(0, 0, $aClientSize[0], $aClientSize[1])
            Sleep("100")
            If $iCheckSum = PixelChecksum(0, 0, $aClientSize[0], $aClientSize[1]) Then
               If $ic = 0 then
                  Local $hTimerC = TimerInit()
                  GetTime()
               EndIf
               $ic += 1
               $ict += 1
            Else
               $ic = 0
               $hTimerC = ""
            EndIf
            Global $iTimeC = Round(TimerDiff($hTimerC)/10,0)
         WEnd
         Global $iTime = Round(TimerDiff($hTimer)/10,0)
         $iTimeT = $iTime - $iTimeC

 

Edited May 4, 2022 by PeterlFF