Last week I shared a post highlighting a common pattern for making API data available through dataflows in Power BI, and included a few diagrams to show how a customer was implementing this pattern.
In the post I mentioned that I was simplifying things a bunch to only focus on the core pattern. One of the things I didn’t mention is that the diagrams I shared were just one piece of the puzzle. Another part was the need to define dataflows in one workspace, and then use those as a template for creating other dataflows in bulk.
This is simple enough to do via the Power BI portal for an individual dataflow, but if you need to do it for every dataflow in a workspace, you might need a little more power – PowerShell, to be specific.
The rest of this post is not from me – it’s from the dataflows engineering team. it describes a set of PowerShell scripts they’ve published on GitHub, and which address this specific problem. The rest is pretty self-explanatory, so I’ll just mention that these are unsupported scripts presented as-is, and I’ll let the rest speak for itself.
Microsoft Power BI dataflows samples
The document below describes the various PowerShell scripts available for Power BI dataflows. These rely on the Power BI public REST APIs and the Power BI PowerShell modules.
Power BI Dataflow PowerShell scripts
Below is a table of the various Power BI PowerShell modules found in this repository.
Description | Module Name | Download |
Export all dataflows from a workspace | ExportWorkspace.ps1 | GitHub Location |
Imports all dataflows into a workspace | ImportWorkspace.ps1 | GitHub Location |
Imports a single dataflow | ImportModel.ps1 | GitHub Location |
For more information on Powershell support for Power BI, please visit powerbi-powershell on GitHub
Supported environments and PowerShell versions
- Windows PowerShell v3.0 and up with .NET 4.7.1 or above.
- PowerShell Core (v6) and up on any OS platform supported by PowerShell Core.
Installation
- The scripts depend on the MicrosoftPowerBIMgmt module which can be installed as follows:
Install-Module -Name MicrosoftPowerBIMgmt
If you have an earlier version, you can update to the latest version by running:
Update-Module -Name MicrosoftPowerBIMgmt
- Download all the scripts from the GitHub Location into a local folder.
- Unblock the script by right click on the files and select “Unblock” after you download. Otherwise you might get a warning when you run the script.
Uninstall
If you want to uninstall all the Power BI PowerShell cmdlets, run the following in an elevated PowerShell session:
Get-Module MicrosoftPowerBIMgmt* -ListAvailable | Uninstall-Module -Force
Usage
The APIs below supports two optional parameters:
- -Environment: A flag to indicate specific Power BI environments to log in to (Public, Germany, USGov, China, USGovHigh, USGovMil). Default is Public
- -V: A flag to indicate whether to produce verbose output. Default is false.
Export workspace
Exports all the dataflow model.json from a Power BI workspace into a folder:
.\ExportWorkspace.ps1 -Workspace "Workspace1" -Location C:\dataflows
Import workspace
Imports all the dataflow model.json from a folder into a Power BI workspace. This script also fixes the reference models to point to the right dataflow in the current workspace:
.\ImportWorkspace.ps1 -Workspace "Workspace1" -Location C:\dataflows -Overwrite
Import dataflow
Imports a dataflow model.json into a Power BI workspace:
.\ImportModel.ps1 -Workspace "Workspace1" -File C:\MyModel.json -Overwrite
thanks, very interesting. Still looking for a way to automated export M-Script stuff from PBIX and create Dataflow in PBI Service…hopefully this will come in future, would be great for the lift & shift story 😉 also lift & Shift M-Script Code from PBIX to Azure Analysis or PBI Premium Partitions
LikeLike
That would be pretty cool,. 😉
I have not done this end to end, but there are options for extracting M queries from a PBIX (see https://duckduckgo.com/?q=extract+M+queries+from+PBIX) which you could use to build a model.json file and then import as a new dataflow using the existing dataflow APIs or UX.
LikeLike
Pingback: Automatically refresh dataset when dataflow refresh completes – BI Polar