Over the last few weeks we have been working on a Dataflow extension for our Power BI templates. The idea is to extract the existing queries from the Power BI Desktop and provide them as centrally prepared data in a Dataflow. During our development, I developed a small PowerShell script that I have now released on GitHub.
The Powershell script that can be called as an external tool from Power BI Desktop, which then extracts the Power Queries via the TOM (Tabular Object Model) and stores them in the Dataflow JSON format. The exported Dataflow JSON file can then be uploaded to the Power BI Portal as Dataflow. To do this, create a new dataflow in the workspace with the Import Datamodel option.
Please know, that everything I have created and shared on the blogpost and on GitHub is based on best effort. No rights can be derived, as well as I am not liable for the use or misuse of the solution or possible damage resulting from this. Use of the solutions and execution of the scripts is all on your own risk and your own responsibility.
I want to shortly describe how this tool works and what you can expect.
- Open a Power BI Desktop file whose Power Query transformations you want to export to a Dataflow.
- In the Top Ribbon under External Tools, click Export to Dataflow.
- After the click a PowerShell window opens which converts the Power Query transformations into the Dataflow JSON format.
During the first execution it may be necessary to agree to the installation of nuget and the package source for the installation of the latest Microsoft.AnalysisServices.Tabular.dll version.
- Then the script asks for the location where the Dataflow JSON file should be exported.
- The exported Dataflow JSON file can then be uploaded to the Power BI Portal as Dataflow. To do this, create a new dataflow in the workspace with the Import Datamodel option.
Download everything you need from this GitHub repository.
– External Tool integration file: export2dataflow.pbitool.json
– The PowerShell script: Export2Dataflow.ps1
Copy the External Tool integration file
The External Tool integration file is needed to get the button in the Power BI ribbon. In order to achieve this, you need to copy the export2dataflow.pbitool.json file in the External Tools folder. For me this location was: C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools
While copying the files, it can be that Windows asks you to login with admin privileges before you can continue. This is mandatory to copy the files. If you cannot do this yourself, please contact your administrator.
Copy the PowerShell script
Create a subfolder Export2Dataflow in the C:\Program Files\ folder and copy the PowerShell script Export2Dataflow.ps1 file into it.
Identical to the previous step, Windows may ask you to authenticate with administrator privileges before you can proceed.
Restart Power BI Desktop
You have applied all required steps by now. A new button will appear in the Power BI Desktop top ribbon for External Tools. In case you had Power BI running already, please restart Power BI desktop first.
Did something not workout as expected for you, kindly check the FAQ to see if your question is already listed there. If not, please let me know.
I hope you have fun and enjoy this little Power BI helper tool.
Marc Lelijveld, for his tutorial on creating external tools for Power BI and his GitHub repository External-Tools-Model-Documentation, from which I was able to adopt a lot.
Website – Blogpost – twitter – LinkedIn – Github
Julian Kaiser, for reviewing, testing and adding some features to the script.