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.
Disclaimer
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.
Usage
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.
Getting Started
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.
Acknowledgements
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.
LinkedIn
Your tool has been added to Business Ops as apart of our collection of external tools. you tool will be released in version 1.0.7
Wow thanks, this will make installation extremely easier.
Hi! I tried your Tool, but right after I ran Export from External Tool, some window appears for like 1s and disappears. No PowerShell window or else, happens nothing.
I just got newest Version from Power BI, doest it make difference?
Thanks in advance.
Mirzet
Hi Mirzet,
have you already tried the following point from the FAQ?
I clicked the button, saw the PowerShell window flickering on the screen, but nothing happened
Most likely, this is caused by the execution policies for PowerShell configured on the computer. As far as I know, this is not something I can change in my script, but has everything to do with the setup of your PC or how your company configured it. This results in the fact that PowerShell.exe application did start, but it did not execute the script, because this was prevented by the policy. The solution to fix this, is changing the execution policy (which is a register thing on your computer. Please know, that this is all at your own risk! I cannot take any responsibility for this.
The following steps might help you
1. Open PowerShell.exe manually
2. Execute Get-ExecutionPolicy -List
3. As a result, you see the current configuration of your execution policies. Most probably everything is set to undefined at this moment.
4. The easiest way to fix this, is by changing the execution policy for the current user. The ensures that this does not happen again. We need to do this by setting it to Unrestricted by executing this task: Set-ExecutionPolicy Unrestricted -Scope CurrentUser (all on your own responsibility and risk!)
5. Confirm that you want to change this.
6. Close PowerShell and Power BI Desktop Try again if it works now.