PowerShell How to delete rows in dataset

kdixon5490 — Sat, 26 Sep 2020 02:18:40 GMT

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-eb32b273a56e/rows?key=z%2BLI4fQkPeYrUHDP5bDCkMBnHVsVxskJB48B2P0Ui3hOJJOuNcbYxZn9%2BD2gKJPp90o1qedZ7hd1p74V8WmZWw%3D%3D"

#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();

Re: PowerShell How to delete rows in dataset

d_gosbell — Mon, 28 Sep 2020 22:14:38 GMT

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?

Re: PowerShell How to delete rows in dataset

DavisBI — Thu, 22 Oct 2020 03:54:54 GMT

Hi, kdixon5490 ,

Why don't use REST API?

topic PowerShell How to delete rows in dataset in Report Server

PowerShell How to delete rows in dataset

Re: PowerShell How to delete rows in dataset

Re: PowerShell How to delete rows in dataset