PowerShell How to delete rows in dataset

[kdixon5490]

I currently have a powershell that runs to update my data.   I need to modify to delete all the rows in the dataset first and then get all new.   I've NEVER used powershell so I''m a little lost.   I found the code on a site and it works perfectly but I need to add in the piece to delete all rows first.    Below is my script.   Can someone PLEASE PLEASE add in the piece I need?

$dataSource = 'XXXXXX' #Server Name
$userName = 'XXXXX' #SQL User Name
$password = 'XXXXXX' #SQL User Password
$database = 'XXXXXXX' #Database Name
#Uncomment below connection string to use windows credentials
$connectionString = "Data Source=$dataSource;Initial Catalog=$database;Integrated Security=SSPI"
#Uncomment below connection string to use SQL Server authentication
#$connectionString ="Server=$dataSource;Database=$database;trusted_connection=false; user id =$userName;Password=$password;Integrated Security=False"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

$query = '
SELECT * from BI_SOLD_RECAP;'
$command = $connection.CreateCommand()
$command.CommandText = $query
$dataset = $command.ExecuteReader()

#paste the endpoint URL which you have inside Power BI
$endpoint = "https://api.powerbi.com/beta/54e2bd33-9277-490e-9932-e1b5a2cfabb2/datasets/f0ee56bf-5c15-45e1-85a2-e..."

#push data to Power BI
while ($dataset.Read()) {
$payload = @{
"EventCode" =$dataset['EventCode']
"EventName" =$dataset['EventName']
"EventDetail" =$dataset['EventDetail']
"PerformanceID" =$dataset['PerformanceID']
"PerformanceName" =$dataset['PerformanceName']
"PerfDateTime" =$dataset['PerfDateTime']
"SalesChannel" =$dataset['SalesChannel']
"SaleDateOnly" =$dataset['SaleDateOnly']
"SaleDate_DOW" =$dataset['SaleDate_DOW']
"SaleDate_Month" =$dataset['SaleDate_Month']
"Quantity" =$dataset['Quantity']
"ServiceFees" =$dataset['ServiceFees']
"ServiceFeesQuantity" =$dataset['ServiceFeesQuantity']
"DeliveryFees" =$dataset['DeliveryFees']
"DeliveryFeesQuantity" =$dataset['DeliveryFeesQuantity']
"TicketCost" =$dataset['TicketCost']
"TotalAmount" =$dataset['TotalAmount']

}
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))
}
$connection.Close();
$connection.Dispose();

[DavisBI]

Hi, @kdixon5490 ,

Why don't use REST API?

[d_gosbell]

---

@kdixon5490 wrote:

I currently have a powershell that runs to update my data.   I need to modify to delete all the rows in the dataset first and then get all new.   

---

I don't think this is possible using a script unless you are using a premium workspace. But this is exactly what a standard scheduled refresh does. Have you tried just configuring a normal scheduled refresh?