This blog will show you how to load data into a Power BI real-time data set using Powershell.
The following code load to a Power BI dataset from a table in the adventureworks database
#Variables - details of the connection, stored procedure and parameters
$connectionString = "server=localhost;database='Adventureworksdw2017';trusted_connection=true;";
$storedProcedureCall = [your store procedure;
#SQL Connection - connection to SQL server
$sqlConnection = new-object System.Data.SqlClient.SqlConnection;
$sqlConnection.ConnectionString = $connectionString;
#SQL Command - set up the SQL call
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$sqlCommand.Connection = $sqlConnection;
$sqlCommand.CommandText = $storedProcedureCall;
#SQL Adapter - get the results using the SQL Command
$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCommand
$dataSet = new-object System.Data.Dataset
$recordCount = $sqlAdapter.Fill($dataSet)
#Close SQL Connection
$sqlConnection.Close();
#Get single table from dataset
$data = $dataSet.Tables[0]
$endpoint = [your dataset url]
#Loop through each row of data and create a new file
#The dataset contains a column named FileName that I am using for the name of the file
foreach($row in $data)
{
$payload = @{
"ProductKey" =$row.Item("ProductKey")
"SalesAmount" =$row.Item("SalesAmount")
}
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))
write-Host $row.Item("ProductKey")
}
at this link the script
Iscriviti a:
Commenti sul post (Atom)
-
Let's see how the power query cache works (in both Excel and Power BI Desktop). A "persistent cache" stored on disk, when refr...
-
As we start the new year, we’re excited to announce we’re rolling out report sharing to users worldwide. Now reports and dashboards have the...
-
The composite model expects to have huge tables as a DirectQuery source and some smaller tables in import mode. When we talk about huge tabl...
Nessun commento:
Posta un commento