Automate Power Query in Excel

The code below works, but not like I would like. I need to find a way to assign the query to a listobject. If you run the code you will see the connection only is made [under Data=> queries and conections] and the results of the query appear in the Sunrise tab. I have tried

$qt = $c.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcExternal,$cSource,$null,$c.Range("$A$1")).QueryTable

#$qt.CommandText = $SQL

and other combinations, but just returns errors. This can be done in VBA.

$mcode = @'
    Source = Web.Page(Web.Contents("|MTH|&year=2021")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"SUN", type text}, {"MON", type text}, {"TUE", type text}, {"WED", type text}, {"THU", type text}, {"FRI", type text}, {"SAT", type text}})
    #"Changed Type"
$mon = "April" 
$qry = "Sunrise" 
$cn = "Query - "+$qry
$mcode = $mcode.Replace("|MTH|",$mon)

$a = New-Object -ComObject Excel.Application
$a.Visible = $true
$a.ScreenUpdating = $true
$a.UserControl = $true
$a.DisplayAlerts = $false

$b = $a.workbooks.add()
$c = $b.Worksheets.Item(1)
$c.Name = $qry
$cSource = "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=$qry;Extended Properties=''"

$SQL = "Select * FROM [$qry]"
$a.ActiveWorkBook.Queries.Add($qry,$mcode,"Sunrise Query")
$qt = $c.QueryTables.Add($cSource, $c.Range("A1"), $SQL)

#do other things, save workkbook


No Data
Reply Children
No Data