PowerBIETL.psm1

A PowerShell module to quickly copy data from PBI Desktop into SQL Server

Sample of usage here:

https://ruiromanoblog.wordpress.com/2016/04/21/use-power-bi-desktop-as-an-etl-tool/

PowerBIPS.psm1

A PowerShell module for the new PowerBI developer REST APIs.

More samples of usage here:

Module also available on PowerShell Gallery:

https://www.powershellgallery.com/packages/PowerBIPS

To install just type "Install-Module -Name PowerBIPS"

Cmdlets present in the module:

Cmdlet	Description
Out-PowerBI	The most easy way for you to send data into PowerBI
Get-PBIAuthToken	Gets the authentication token required to communicate with the PowerBI APIs
Set-PBIGroup	Set's the scope to the group specified. Most of PowerBIPS cmdlets will execute over the setted group.
Get-PBIGroup	Gets the PowerBI groups in the user workspace
Get-PBIGroupUsers	Gets the users that are members of a group
New-PBIDataSet	Create a new DataSet
Add-PBITableRows	Add's a collection of rows into a powerbi dataset table in batches
Get-PBIDataSet	Gets a DataSet collection, includes definition and tables
Test-PBIDataSet	Test the existence of a DataSet by name
Update-PBIDataset	Send command to refresh one or more datasets
Get-PBIDatasetRefreshHistory	Get refresh history of one or more datasets
Clear-PBITableRows	Delete all the rows of a PowerBI dataset table
Update-PBITableSchema	Updates a table schema
Get-PBIDashboard	Gets a Dashboard collection
Get-PBIDashboardTile	Gets a Tile collection from a dashboard
Get-PBIReport	Gets a Report collection
Export-PBIReport	Download reports as PBIX files
Copy-PBIReports	Duplicate reports by suppling a list of the reports to copy
Set-PBIReportsDataset	Rebind reports to another dataset on the same workspace
Get-PBIDatasetParameters	Gets all parameters available in one or more datasets
Set-PBIDatasetParameters	Change parameter values in one or more datasets

For a better experience please copy this module on your UserProfile directory:

%USERPROFILE%\Documents\WindowsPowershell\Modules\PowerBIPS

Or just import it to your PowerShell session by typing:

Import-Module ".\Modules\PowerBIPS" -Force

Sample Script 1 (Send CSV Data To PowerBI)

while($true)
{
	# Iterate each CSV file and add to a hashtable with a key for each table that will later be sent to PowerBI
	
	Get-ChildItem "$currentPath\CSVData" -Filter "*.csv" |% {
		
		$tableName = $_.BaseName.Split('.')[0]

		$data = Import-Csv $_.FullName					
		
		# Send data to PowerBI
		
		$data | Out-PowerBI -dataSetName "CSVSales" -tableName "Sales" -types @{"Sales.OrderDate"="datetime"; "Sales.SalesAmount"="double"; "Sales.Freight"="double"} -batchSize 300 -verbose	
		
		# Archive the file
		
		Move-Item $_.FullName "$currentPath\CSVData\Archive" -Force
	}
	
	Write-Output "Sleeping..."
	
	Sleep -Seconds 5
}

Sample Script 2 (Manual DataSet creation)

cls

# Get the authentication token using ADAL library (OAuth)

$authToken = Get-PBIAuthToken

# Test the existence of the dataset
if (-not (Test-PBIDataSet -authToken $authToken -dataSetName "TestDataSet"))
{
	# If cannot find the DataSet create a new one with this schema
	$dataSetSchema = @{
		name = "TestDataSet"	
	    ; tables = @(
			@{ 	name = "TestTable"
				; columns = @( 
					@{ name = "Id"; dataType = "Int64"  }
					, @{ name = "Name"; dataType = "String"  }
					, @{ name = "Date"; dataType = "DateTime"  }
					, @{ name = "Value"; dataType = "Double"  }
					) 
			})
	}	
	
	$createdDataSet = New-PBIDataSet -authToken $authToken -dataSet $dataSetSchema -Verbose
}
else
{
	# Clear all the rows of the dataset table	
	Clear-PBITableRows -authToken $authToken -dataSetName "TestDataSet" -tableName "TestTable" -Verbose
}

# Create a array of sample rows with the same schema of the dataset table
$sampleRows = 1..53 |% {	
	@{
		Id = $_
		; Name = "Record $_"
		; Date = [datetime]::Now
		; Value = (Get-Random -Minimum 10 -Maximum 1000)
	}
}

# Insert the sample rows in batches of 10
$sampleRows | Add-PBITableRows -authToken $authToken -dataSetName "TestDataSet" -tableName "TestTable" -batchSize 10 -Verbose

Out-PowerBI - Simply send any data to PowerBI in a single line of code

# Upload local computer windows process data to PowerBI

Get-Process | Out-PowerBI -verbose

# Upload CSV data to PowerBI dataset named "CSVSales" and with the types specified

Import-Csv "c:\csvData.csv" | Out-PowerBI -dataSetName "CSVSales" -tableName "Sales" -types @{"Sales.OrderDate"="datetime"; "Sales.SalesAmount"="double"; "Sales.Freight"="double"} -batchSize 300 -verbose

Get-PBIAuthToken - Get's the OAuth PowerBI Authentication Token

$authToken = Get-PBIAuthToken

# To use username+password authentication you need to create an Azure AD Application and get it's id

$authTokenWithUsername = Get-PBIAuthToken -ClientId "C0E8435C-614D-49BF-A758-3EF858F8901B" -Credential (Get-Credential -username "<username>"

$authTokenWithUsernameAndPassword = Get-PBIAuthToken -ClientId "C0E8435C-614D-49BF-A758-3EF858F8901B" -Credential (new-object System.Management.Automation.PSCredential("<username>",(ConvertTo-SecureString -String "<password>" -AsPlainText -Force)))

Get-PBIGroup - Get's the PowerBI groups in the user workspace

$authToken = Get-PBIAuthToken

$group = Get-PBIGroup -authToken $authToken -name "SalesGroup"

Set-PBIGroup - Set's the scope to the group specified. Most of PowerBIPS cmdlets will execute over the setted group.

$authToken = Get-PBIAuthToken

$group = Get-PBIGroup -authToken $authToken -name "SalesGroup"

# Gets the datasets of the group

Set-PBIGroup -id $group.id

$dataSetsOfGroup = Get-PBIDataSet -authToken $authToken

# Clear the group and all the requests now are for the default workspace

Set-PBIGroup -clear

Get-PBIGroupUsers - Gets the users that are members of a group

$authToken = Get-PBIAuthToken

Set-PBIGroup -authToken $authToken -name "Sales"

Get-PBIGroupUsers -authToken $authToken

Get-PBIDataSet - Get a DataSet or a List of DataSets

# All DataSets
$dataSets = Get-PBIDataSet -authToken $authToken

# By Name
$dataSets = Get-PBIDataSet -authToken $authToken -dataSetName "TestDataSet"

# With tables and definition (retentionPolicy,...)
$dataSets = Get-PBIDataSet -authToken $authToken -dataSetName "TestDataSet" -includeTables -includeDefinition

Test-PBIDataSet - Test the existence of a DataSet

if (Test-PBIDataSet -authToken $authToken -dataSetName "TestDataSet")
{
	Write-Host "true"
}
else
{
	Write-Host "false"
}

Update-PBIDataset - Send command to refresh one or more datasets

$authToken = Get-PBIAuthToken

Set-PBIGroup -authToken $authToken -name "Sales"

Update-PBIDataset -authToken $authToken -datasetNames "Dataset1", "Dataset2"

Get-PBIDataSetRefreshHistory - Get refresh history of one or more datasets

$authToken = Get-PBIAuthToken

Set-PBIGroup -authToken $authToken -name "Sales"

$hist=Get-PBIDataSetRefreshHistory -authToken $authToken -top 10 -datasetNames "Dataset1"
$hist.value

New-PBIDataSet - Create a DataSet

$dataSetSchema = @{
	name = "TestDataSet"	
    ; tables = @(
		@{ 	name = "TestTable"
			; columns = @( 
				@{ name = "Id"; dataType = "Int64"  }
				, @{ name = "Name"; dataType = "String"  }
				, @{ name = "Date"; dataType = "DateTime"  }
				, @{ name = "Value"; dataType = "Double"  }
				) 
		})
}	

$createdDataSet = New-PBIDataSet -authToken $authToken -dataSet $dataSetSchema -Verbose

Add-PBITableRows - Add Rows to a table

$sampleRows = 1..53 |% {	
	@{
		Id = $_
		; Name = "Record $_"
		; Date = [datetime]::Now
		; Value = (Get-Random -Minimum 10 -Maximum 1000)
	}
}

# Push the rows into PowerBI in batches of 10 records

$sampleRows | Add-PBITableRows -authToken $authToken -dataSetName "TestDataSet" -tableName "TestTable" -batchSize 10 -Verbose

Clear-PBITableRows - Delete Rows of a table

Clear-PBITableRows -authToken $authToken -dataSetName "TestDataSet" -tableName "TestTable" -Verbose

Update-PBITableSchema - Update a Table Schema

$tableSchema =  @{ 
	name = "Sales"
	; columns = @( 
		@{ name = "Col1"; dataType = "Int64"  }
		, @{ name = "Col2"; dataType = "string"  }
		, @{ name = "NewColumn"; dataType = "string"  }
		) 
}

Update-PBITableSchema -authToken $authToken -dataSetId "<dataSetId>" -table $tableSchema -verbose

Get-PBIDashboard - Gets a Dashboard collection

$dashboards = Get-PBIDashboard

Get-PBIDashboardTile - Gets a Tile collection

$tiles = Get-PBIDashboardTile -dashboardId "XXX-XXX-XXX"

Get-PBIReport - Gets a Report collection

$reports = Get-PBIReport

Export-PBIReport - Download reports as PBIX files

$authToken = Get-PBIAuthToken

Set-PBIGroup -authToken $authToken -name "Sales"

Export-PBIReport -authToken $authToken -reportNames "Report1","Report2" -destinationFolder "C:\Reports"

Copy-PBIReports - Duplicate reports by suppling a list of the reports to copy

$authToken = Get-PBIAuthToken

Set-PBIGroup -authToken $authToken -name "Sales"

$reports= @(
    @{
        originalReportId="2073307d-3bxd-4165-916e-ca0aa2b95ed9"
        targetname="Copy of Report 1"
        targetWorkspaceId="b8cd99e3-d453-49b9-abd5-b34aef41571c"
        targetModelName="Dataset 1"
    }
    @{
        originalReportName="Report 2"
        targetname="Copy of Report 2"
        targetWorkspaceName="Sales"
        targetModelID="38d98386-31cf-4590-9a75-8701fb17ef16"
    }
)

$newReportData = Copy-PBIReports -authToken $authToken -reportsObj $reports -Verbose

$newReportData

Set-PBIReportsDataset - Rebind reports to another dataset on the same workspace

$authToken = Get-PBIAuthToken

Set-PBIGroup -authToken $authToken -name "Sales"

Set-PBIReportsDataset -authToken $authToken -reportNames "Report1","Report2" -datasetName "SalesDataset"

Get-PBIDatasetParameters - Gets all parameters available in one or more datasets.

$authToken = Get-PBIAuthToken

Set-PBIGroup -authToken $authToken -name "Sales"

$parameters = Get-PBIDatasetParameters -authToken $authToken -datasetNames "SomeDataset"
$parameters | Format-Table

Set-PBIDatasetParameters - Change parameter values in one or more datasets.

$authToken = Get-PBIAuthToken

Set-PBIGroup -authToken $authToken -name "Sales"

$parameters = @(
    @{
        name="SomeParameter"
        newValue="SomeValue"
    }
)

Set-PBIDatasetParameters -authToken $authToken -parameters $parameters -datasetNames "SomeDataset"

fmms / powerbi-powershell-modules Goto Github PK

powerbi-powershell-modules's Introduction